﻿/*ESRPConnectionString*/
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
/*Modificari pana la data de 01.01.2011*/
/**/


IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Applications]') AND type in (N'U'))
BEGIN 

CREATE TABLE [dbo].[aspnet_Applications](
	[ApplicationName] [nvarchar](256) NOT NULL,
	[LoweredApplicationName] [nvarchar](256) NOT NULL,
	[ApplicationId] [uniqueidentifier]  DEFAULT (newid()) NOT NULL,
	[Description] [nvarchar](256) NULL,
	[System] [bit] NULL DEFAULT ((0)),
	[IDIntern] [varchar](50) NULL DEFAULT ((0)),
	[MenuName] [varchar](50) NULL,
	[NumeDLL] [varchar](100) NULL,
 CONSTRAINT [PK__aspnet_Applicati__69727791] PRIMARY KEY NONCLUSTERED 
(
	[ApplicationId] ASC
) ON [PRIMARY],
 CONSTRAINT [UQ__aspnet_Applicati__6A669BCA] UNIQUE NONCLUSTERED 
(
	[LoweredApplicationName] ASC
) ON [PRIMARY],
 CONSTRAINT [UQ__aspnet_Applicati__6B5AC003] UNIQUE NONCLUSTERED 
(
	[ApplicationName] ASC
) ON [PRIMARY]
) ON [PRIMARY]

END
GO


IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[admin_ApplicationMenu]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[admin_ApplicationMenu](
	[IDMeniuAplicatie] [int] IDENTITY(1,1) NOT NULL,
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[IdMeniuParinte] [int] NULL,
	[Ordine] [smallint] NOT NULL,
	[HandlerName] [varchar](50) NULL,
	[Tag] [varchar](50) NULL,
	[Vizibil] [varchar](50) NULL,
 CONSTRAINT [PK_admin_ApplicationMenu] PRIMARY KEY CLUSTERED 
(
	[IDMeniuAplicatie] ASC
)ON [PRIMARY],
CONSTRAINT [FK_admin_ApplicationMenu_aspnet_Applications] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
) ON [PRIMARY]


END
GO



DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [aspnet_Applications] WHERE [LoweredApplicationName]='edocument'
IF @Cate= 0 
BEGIN
	INSERT INTO [aspnet_Applications] ([ApplicationName],[LoweredApplicationName],[ApplicationId],[Description],[System],[IDIntern],[MenuName],[NumeDLL])VALUES('eDocument','edocument','E4045C52-E820-4C37-85AA-886D85919E22','Document Electronic',1,'06','eDocument','eDocument.dll')
END

SELECT @Cate = COUNT(*) FROM [aspnet_Applications] WHERE [LoweredApplicationName]='sankit'
IF @Cate= 0 
BEGIN
	INSERT INTO [aspnet_Applications] ([ApplicationName],[LoweredApplicationName],[ApplicationId],[Description],[System],[IDIntern],[MenuName],[NumeDLL])VALUES('sankit','sankit','1498f4bb-8c6d-401f-ae2e-05601f279aa3','Chitantier Medical',1,'07','Chitantier','SanKit.dll')
END

SELECT @Cate = COUNT(*) FROM [aspnet_Applications] WHERE [LoweredApplicationName]='ebuget'
IF @Cate= 0 
BEGIN
	INSERT INTO [aspnet_Applications] ([ApplicationName],[LoweredApplicationName],[ApplicationId],[Description],[System],[IDIntern],[MenuName],[NumeDLL])VALUES('eBuget','ebuget','ebe8f4bb-8c6d-401f-ae2e-05601f279ebe','Buget Multianual',1,'08','Buget','eBuget.exe')
END


SELECT @Cate = COUNT(*) FROM [aspnet_Applications] WHERE [LoweredApplicationName]='exbug'
IF @Cate= 0 
BEGIN
	INSERT INTO [aspnet_Applications] ([ApplicationName],[LoweredApplicationName],[ApplicationId],[Description],[System],[IDIntern],[MenuName],[NumeDLL])VALUES('ExBug','exbug','8489910A-6599-436A-B3E5-21B6DB74A7BE','Executie Bugetara',1,'05','ExBug','ExBug.dll')
END

SELECT @Cate = COUNT(*) FROM [aspnet_Applications] WHERE [LoweredApplicationName]='modbalcon'
IF @Cate= 0 
BEGIN
	INSERT INTO [aspnet_Applications] ([ApplicationName],[LoweredApplicationName],[ApplicationId],[Description],[System],[IDIntern],[MenuName],[NumeDLL])VALUES('ModBalCon','modbalcon','414DDC83-DC7F-437D-AA83-3E9E3A48CB42','Contabilitate Tribuna',1,'99','ModBalCon','ModBalCon.dll')
END




GO

DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [admin_ApplicationMenu] WHERE [ApplicationId]='E4045C52-E820-4C37-85AA-886D85919E22'
IF @Cate<> 0 
begin
	DELETE FROM [admin_ApplicationMenu] WHERE [ApplicationId] = 'E4045C52-E820-4C37-85AA-886D85919E22'
end 
go

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(6001,'E4045C52-E820-4C37-85AA-886D85919E22','Explorer',NULL,1,'Explorer',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(6002,'E4045C52-E820-4C37-85AA-886D85919E22','Asocieri',NULL,2,'Asocieri',NULL,NULL)
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
GO


DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [admin_ApplicationMenu] WHERE [ApplicationId]='ebe8f4bb-8c6d-401f-ae2e-05601f279ebe'
IF @Cate<> 0 
begin
	DELETE FROM [admin_ApplicationMenu] WHERE [ApplicationId] = 'ebe8f4bb-8c6d-401f-ae2e-05601f279ebe'
end 
go

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(7001,'ebe8f4bb-8c6d-401f-ae2e-05601f279ebe','Buget Multianual',NULL,1,'Buget_Multianual',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(7002,'ebe8f4bb-8c6d-401f-ae2e-05601f279ebe','Asocieri',NULL,2,'Asocieri',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(7003,'ebe8f4bb-8c6d-401f-ae2e-05601f279ebe','Setari',NULL,3,'Setari',NULL,NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(7050,'ebe8f4bb-8c6d-401f-ae2e-05601f279ebe','Conturi Bugetate',7003,50,'Conturi_Bugetate',NULL,NULL)
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
GO


DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [admin_ApplicationMenu] WHERE [ApplicationId]='8489910A-6599-436A-B3E5-21B6DB74A7BE'
IF @Cate<> 0 
begin
	DELETE FROM [admin_ApplicationMenu] WHERE [ApplicationId] = '8489910A-6599-436A-B3E5-21B6DB74A7BE'
end 
go

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2082,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Buget',NULL,10,'Buget',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2083,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Angajament Bugetar',NULL,15,'AB',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2084,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Angajament Legal',NULL,20,'AL',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2085,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Propunere Angajare Cheltuiala',NULL,14,'PAC',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2086,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Ordonantare',NULL,25,'Ordonantari',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2087,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Liste',NULL,26,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2088,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Setari',NULL,30,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2089,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Parametrizare modul',2088,100,'Parametrizare',NULL,NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2689,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Registrul proiectelor de operatiuni',2087,205,'Registrul_Proiecte',NULL,NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2589,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Articole,Alineate',2088,105,'ArtAl',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2590,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Capitole',2088,110,'Jurnale',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2591,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Parteneri',2088,115,'Furnizori',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2592,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Responsabili CFP',2088,120,'ResponsabilCFP',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2593,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Banci',2088,125,'AB',NULL,NULL)


SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
GO


IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[TipAdresa]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[TipAdresa](
		[IDTipAdresa] [smallint] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](100) NOT NULL,
	 CONSTRAINT [PK_TipAdresa] PRIMARY KEY CLUSTERED 
	(
		[IDTipAdresa] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]
	SET IDENTITY_INSERT [TipAdresa] ON
	INSERT INTO [TipAdresa] ([IDTipAdresa],[Denumire])VALUES(1,'Principala')
	INSERT INTO [TipAdresa] ([IDTipAdresa],[Denumire])VALUES(2,'Filiala')
	INSERT INTO [TipAdresa] ([IDTipAdresa],[Denumire])VALUES(3,'Facturare')
	INSERT INTO [TipAdresa] ([IDTipAdresa],[Denumire])VALUES(4,'Livrare')
	INSERT INTO [TipAdresa] ([IDTipAdresa],[Denumire])VALUES(5,'Sediu Social')
	SET IDENTITY_INSERT [TipAdresa] OFF
END
GO

IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[TipContact]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[TipContact](
		[IDTipContact] [smallint] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](100) NOT NULL,
	 CONSTRAINT [PK_TipContact] PRIMARY KEY CLUSTERED 
	(
		[IDTipContact] ASC
	) ON [PRIMARY]
	) ON [PRIMARY]
	SET IDENTITY_INSERT [TipContact] ON
	INSERT INTO [TipContact] ([IDTipContact],[Denumire])VALUES(1,'Persona Contact')
	INSERT INTO [TipContact] ([IDTipContact],[Denumire])VALUES(2,'Factor Decizie')
	INSERT INTO [TipContact] ([IDTipContact],[Denumire])VALUES(3,'Neimportant')
	INSERT INTO [TipContact] ([IDTipContact],[Denumire])VALUES(4,'Partener')
	SET IDENTITY_INSERT [TipContact] OFF
END
GO



IF  NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[Contact]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[Contact](
		[IDContact] [int] IDENTITY(1,1) NOT NULL,
		[Nume] [varchar](50) NULL,
		[Prenume] [varchar](50) NULL,
		[IDTipContact] [smallint] NULL,
		[CNP] [decimal](13, 0) NULL,
		[StareCivila] [varchar](2) NULL,
		[Functie] [varchar](50) NULL,
		[Departament] [varchar](50) NULL,
		[TipAI] [varchar](2) NULL,
		[SerieAI] [varchar](10) NULL,
		[NumarAI] [decimal](10, 0) NULL,
		[IDPartener] [bigint] NULL,
	 CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED 
	(
		[IDContact] ASC
	) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_Parteneri] FOREIGN KEY([IDPartener])
	REFERENCES [dbo].[Parteneri] ([IDPartener])

	ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_Parteneri]

	ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_TipContact] FOREIGN KEY([IDTipContact])
	REFERENCES [dbo].[TipContact] ([IDTipContact])

	ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_TipContact]

END
GO


IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[InfoContact]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[InfoContact](
		[IDInfoContact] [int] IDENTITY(1,1) NOT NULL,
		[Telefon] [varchar](50) NULL,
		[Mobil] [varchar](50) NULL,
		[TelefonPersonal] [varchar](50) NULL,
		[Fax] [varchar](50) NULL,
		[Email] [varchar](150) NULL,
		[YMID] [varchar](50) NULL,
		[WebSite] [varchar](150) NULL,
		[IDContact] [int] NULL,
	 CONSTRAINT [PK_InfoContact] PRIMARY KEY CLUSTERED 
	(
		[IDInfoContact] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[InfoContact]  WITH CHECK ADD  CONSTRAINT [FK_InfoContact_Contact] FOREIGN KEY([IDContact])
	REFERENCES [dbo].[Contact] ([IDContact])

	ALTER TABLE [dbo].[InfoContact] CHECK CONSTRAINT [FK_InfoContact_Contact]

END
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[Tari]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[Tari](
		[IDTara] [int] IDENTITY(1,1) NOT NULL,
		[NUME] [varchar](50) NULL,
		[SIGLA] [char](2) NULL,
		[preftel] [varchar](5) NULL,
	 CONSTRAINT [PK_Tari] PRIMARY KEY CLUSTERED 
	(
		[IDTara] ASC
	) ON [PRIMARY]
	) ON [PRIMARY]
	SET IDENTITY_INSERT [Tari] ON
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(529,'Suedia','XX','0046')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(538,'Romania','XX','0040')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(539,'Tunisia','XX','0216')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(548,'Austria','XX','0061')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(549,'Turcia','XX','0090')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(558,'Belarus','XX','')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(559,'Ucraina','XX','')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(568,'Belgia','XX','0032')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(569,'Ungaria','XX','0036')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(578,'Bulgaria','XX','00359')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(579,'Mauritania','XX','')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(588,'Canada','XX','001')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(589,'Taiwan','XX','')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(598,'Cehia','XX','0042')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(608,'China','XX','0086')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(618,'Croatia','XX','0038')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(698,'Danemarca','XX','0045')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(708,'Egipt','XX','0020')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(718,'Elvetia','XX','0041')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(728,'Estonia','XX','')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(738,'Finlanda','XX','00358')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(748,'Franta','XX','0033')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(758,'Georgia','XX','')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(768,'Germania','XX','0049')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(778,'Grecia','XX','0030')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(788,'Irlanda','XX','00353')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(798,'Italia','XX','0039')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(808,'Iugoslavia','XX','0038')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(818,'Japonia','XX','0081')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(828,'Lituania','XX','')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(838,'Luxemburg','XX','00352')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(848,'Malta','XX','00356')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(858,'Marea-britanie','XX','0044')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(868,'Maroc','XX','0021')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(878,'Norvegia','XX','0047')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(888,'Olanda','XX','0031')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(898,'Polonia','XX','0048')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(908,'Rusia','XX','007')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(918,'S.u.a','XX','001')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(928,'Slovacia','XX','00424')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(938,'Slovenia','XX','')
	INSERT INTO [Tari] ([IDTara],[NUME],[SIGLA],[preftel])VALUES(948,'Spania','XX','0034')
	SET IDENTITY_INSERT [Tari] OFF
END
GO

 
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[Adrese]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[Adrese](
		[IDAdresa] [int] IDENTITY(1,1) NOT NULL,
		[IDJudet] [int] NULL,
		[IDLocalitati] [int] NULL,
		[Strada] [varchar](100) NULL,
		[Numar] [varchar](20) NULL,
		[Bloc] [varchar](20) NULL,
		[Scara] [varchar](20) NULL,
		[Apartament] [varchar](20) NULL,
		[IDTara] [int] NULL,
		[Observatii] [varchar](150) NULL,
		[IDPartener] [bigint] NULL,
		[CodPostal] [varchar](6) NULL,
		[IDTipAdresa] [smallint] NULL,
		[Telefon] [varchar](50) NULL,
		[Fax] [varchar](50) NULL,
	CONSTRAINT [PK_Adrese] PRIMARY KEY CLUSTERED 
	(
		[IDAdresa] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[Adrese]  WITH CHECK ADD  CONSTRAINT [FK_Adrese_Judete] FOREIGN KEY([IDJudet])
	REFERENCES [dbo].[Judete] ([IDJudet])

	ALTER TABLE [dbo].[Adrese] CHECK CONSTRAINT [FK_Adrese_Judete]

	ALTER TABLE [dbo].[Adrese]  WITH CHECK ADD  CONSTRAINT [FK_Adrese_Localitati] FOREIGN KEY([IDLocalitati])
	REFERENCES [dbo].[Localitati] ([IDLocalitate])

	ALTER TABLE [dbo].[Adrese] CHECK CONSTRAINT [FK_Adrese_Localitati]

	ALTER TABLE [dbo].[Adrese]  WITH CHECK ADD  CONSTRAINT [FK_Adrese_Parteneri] FOREIGN KEY([IDPartener])
	REFERENCES [dbo].[Parteneri] ([IDPartener])

	ALTER TABLE [dbo].[Adrese] CHECK CONSTRAINT [FK_Adrese_Parteneri]

	ALTER TABLE [dbo].[Adrese]  WITH CHECK ADD  CONSTRAINT [FK_Adrese_Tari] FOREIGN KEY([IDTara])
	REFERENCES [dbo].[Tari] ([IDTara])

	ALTER TABLE [dbo].[Adrese] CHECK CONSTRAINT [FK_Adrese_Tari]

	ALTER TABLE [dbo].[Adrese]  WITH CHECK ADD  CONSTRAINT [FK_Adrese_TipAdresa] FOREIGN KEY([IDTipAdresa])
	REFERENCES [dbo].[TipAdresa] ([IDTipAdresa])

	ALTER TABLE [dbo].[Adrese] CHECK CONSTRAINT [FK_Adrese_TipAdresa]
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Articole' AND COLUMN_NAME = 'PretVanzare')
BEGIN
ALTER TABLE dbo.Articole ADD
	PretVanzare decimal(18, 2) NOT NULL CONSTRAINT DF_Articole_PretVanzare DEFAULT 0

END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ArticoleVariante' AND COLUMN_NAME = 'PretVanzare')
BEGIN
ALTER TABLE dbo.ArticoleVariante ADD
	PretVanzare decimal(18, 2) NOT NULL CONSTRAINT DF_ArticoleVariante_PretVanzare DEFAULT 0

END
GO


DECLARE @Cate2 int
SELECT @Cate2 = COUNT(*) FROM [tipdocument] WHERE [IDTipDocument]=12
IF @Cate2= 0 
BEGIN
	SET IDENTITY_INSERT [tipdocument] ON
	INSERT INTO [tipdocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(12,'Comanda','I',0)
	SET IDENTITY_INSERT [tipdocument] OFF
END
GO

DECLARE @Cate3 int

SELECT @Cate3 = COUNT(*) FROM [admin_ApplicationDocuments] WHERE [IDTipDocument]=12
IF @Cate3= 0 
BEGIN
INSERT INTO [admin_ApplicationDocuments] ([IDTipDocument],[ApplicationID])VALUES(12,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9')
END
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[eTipuriDocumente]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[eTipuriDocumente](
		[IDTipDocument] [int] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](50) NOT NULL,
		[Extensie] [varchar](50) NOT NULL,
		[DllAcces] [varchar](500) NULL,
	 CONSTRAINT [PK_eTipuriDocumente] PRIMARY KEY CLUSTERED 
	(
		[IDTipDocument] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]
	SET IDENTITY_INSERT [etipuridocumente] ON
	INSERT INTO [etipuridocumente] ([IDTipDocument],[Denumire],[Extensie],[DllAcces])VALUES(1,'PDF','pdf',NULL)
	INSERT INTO [etipuridocumente] ([IDTipDocument],[Denumire],[Extensie],[DllAcces])VALUES(2,'Microsoft Excel','xls',NULL)
	INSERT INTO [etipuridocumente] ([IDTipDocument],[Denumire],[Extensie],[DllAcces])VALUES(3,'Microsoft Document','doc',NULL)
	INSERT INTO [etipuridocumente] ([IDTipDocument],[Denumire],[Extensie],[DllAcces])VALUES(4,'Bitmap','bmp',NULL)
	SET IDENTITY_INSERT [etipuridocumente] OFF
END
GO


IF NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[eCatalog]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[eCatalog](
		[IDCatalog] [bigint] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](150) NOT NULL,
		[IDCatalogParinte] [bigint] NULL,
	 CONSTRAINT [PK_eCatalog] PRIMARY KEY CLUSTERED 
	(
		[IDCatalog] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]
END
GO

IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[eDocumente]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[eDocumente](
		[IDDocument] [bigint] IDENTITY(1,1) NOT NULL,
		[Cod] [varchar](20) NOT NULL,
		[Denumire] [varchar](100) NOT NULL,
		[Descriere] [varchar](250) NULL,
		[IDCatalog] [bigint] NULL,
		[IDUserProprietar] [uniqueidentifier] NULL,
		[Public] [bit] NOT NULL,
	 CONSTRAINT [PK_eDocumente] PRIMARY KEY CLUSTERED 
	(
		[IDDocument] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[eDocumente]  WITH CHECK ADD  CONSTRAINT [FK_eDocumente_aspnet_Users] FOREIGN KEY([IDUserProprietar])
	REFERENCES [dbo].[aspnet_Users] ([UserId])
	ALTER TABLE [dbo].[eDocumente] CHECK CONSTRAINT [FK_eDocumente_aspnet_Users]
	ALTER TABLE [dbo].[eDocumente]  WITH CHECK ADD  CONSTRAINT [FK_eDocumente_eCatalog] FOREIGN KEY([IDCatalog])
	REFERENCES [dbo].[eCatalog] ([IDCatalog])
	ALTER TABLE [dbo].[eDocumente] CHECK CONSTRAINT [FK_eDocumente_eCatalog]
	ALTER TABLE [dbo].[eDocumente] ADD  CONSTRAINT [DF_eDocumente_Public]  DEFAULT ((0)) FOR [Public]
END
GO

IF  NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[eVersiuniDocumente]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[eVersiuniDocumente](
		[IDVersiuneDocument] [bigint] IDENTITY(1,1) NOT NULL,
		[IDDocument] [bigint] NOT NULL,
		[Data] [datetime] NOT NULL,
		[Continut] [image] NULL,
		[IDUserProprietar] [uniqueidentifier] NULL,
		[Descriere] [varchar](50) NULL,
		[NumeFisier] [varchar](100) NULL,
		[CaleFisier] [varchar](1000) NULL,
		[IDTipDocument] [int] NOT NULL,
	 CONSTRAINT [PK_eVersiuniDocumente] PRIMARY KEY CLUSTERED 
	(
		[IDVersiuneDocument] ASC
	)ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

	ALTER TABLE [dbo].[eVersiuniDocumente]  WITH CHECK ADD  CONSTRAINT [FK_eVersiuniDocumente_aspnet_Users] FOREIGN KEY([IDUserProprietar])
	REFERENCES [dbo].[aspnet_Users] ([UserId])

	ALTER TABLE [dbo].[eVersiuniDocumente] CHECK CONSTRAINT [FK_eVersiuniDocumente_aspnet_Users]

	ALTER TABLE [dbo].[eVersiuniDocumente]  WITH CHECK ADD  CONSTRAINT [FK_eVersiuniDocumente_eDocumente] FOREIGN KEY([IDDocument])
	REFERENCES [dbo].[eDocumente] ([IDDocument])

	ALTER TABLE [dbo].[eVersiuniDocumente] CHECK CONSTRAINT [FK_eVersiuniDocumente_eDocumente]

	ALTER TABLE [dbo].[eVersiuniDocumente]  WITH CHECK ADD  CONSTRAINT [FK_eVersiuniDocumente_eTipuriDocumente] FOREIGN KEY([IDTipDocument])
	REFERENCES [dbo].[eTipuriDocumente] ([IDTipDocument])

	ALTER TABLE [dbo].[eVersiuniDocumente] CHECK CONSTRAINT [FK_eVersiuniDocumente_eTipuriDocumente]

	ALTER TABLE [dbo].[eVersiuniDocumente] ADD  CONSTRAINT [DF_eVersiuniDocumente_Data]  DEFAULT (getdate()) FOR [Data]

	ALTER TABLE [dbo].[eVersiuniDocumente] ADD  CONSTRAINT [DF_eVersiuniDocumente_IDTipDocument]  DEFAULT ((0)) FOR [IDTipDocument]

END
GO


IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[_GetPtrTextField]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_GetPtrTextField]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[_GetPtrTextField] 
	-- Add the parameters for the stored procedure here
	@tabela varchar(100),
	@pkfield varchar(100),
	@pkvalue varchar(100),
	@camptext varchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @cmd varchar(4000)
	SET @cmd = 'SELECT TEXTPTR('+@camptext+') as [TextPtr] FROM '+@tabela+'  WHERE '+@pkfield+'='+@pkvalue
	EXEC(@cmd)
END
GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[eVersiuneDocumentAdd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[eVersiuneDocumentAdd]
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[eVersiuneDocumentAdd] 
	-- Add the parameters for the stored procedure here
	@idDocument bigint,
	@UserID uniqueidentifier,
	@descriere varchar(50),
	@IdVerDoc int out
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    INSERT INTO [eVersiuniDocumente] ([IDDocument],[Data],[Continut],[IDUserProprietar],[Descriere]) VALUES
    (@idDocument,GETDATE(),'',@UserID,@descriere)
    	
    SELECT  @IdVerDoc= @@identity	
    	
END
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[AngajatiSporuri]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[AngajatiSporuri](
		[IdTipSpor] [int] NOT NULL,
		[IdPersoana] [int] NOT NULL,
		[DeLaData] [datetime] NOT NULL,
		[IdPersoanaSpor] [int] IDENTITY(1,1) NOT NULL,
		[PanaLaData] [datetime] NULL,
		[Valoare] [decimal](18, 2) NOT NULL,
		[Procent] [decimal](6, 2) NOT NULL,
		[ValoarePreaviz] [decimal](18, 2) NOT NULL,
	 CONSTRAINT [PK_AngajatiSporuri] PRIMARY KEY CLUSTERED 
	(
		[IdPersoanaSpor] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]



	ALTER TABLE [dbo].[AngajatiSporuri]  WITH CHECK ADD  CONSTRAINT [FK_PersoaneSporuri_TipSpor] FOREIGN KEY([IdTipSpor])
	REFERENCES [dbo].[TipSpor] ([IDTipSpor])


	ALTER TABLE [dbo].[AngajatiSporuri] CHECK CONSTRAINT [FK_PersoaneSporuri_TipSpor]


	ALTER TABLE [dbo].[AngajatiSporuri]  WITH CHECK ADD  CONSTRAINT [FK_AngajatiSporuri] FOREIGN KEY([IdPersoana])
	REFERENCES [dbo].[Persoane] ([IdPersoana])


	ALTER TABLE [dbo].[AngajatiSporuri] CHECK CONSTRAINT [FK_AngajatiSporuri]

	ALTER TABLE [dbo].[AngajatiSporuri] ADD  CONSTRAINT [DF_PersoaneSporuri_Valoare]  DEFAULT ((0)) FOR [Valoare]

	ALTER TABLE [dbo].[AngajatiSporuri] ADD  CONSTRAINT [DF_PersoaneSporuri_Procent]  DEFAULT ((0)) FOR [Procent]

	ALTER TABLE [dbo].[AngajatiSporuri] ADD  CONSTRAINT [DF_PersoaneSporuri_ValoarePreaviz]  DEFAULT ((0)) FOR [ValoarePreaviz]

END
GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[FK_PersoaneSporuri_TipSpor]') AND type in(N'F'))
ALTER TABLE dbo.AngajatiSporuri DROP CONSTRAINT FK_PersoaneSporuri_TipSpor
GO 

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Sporuri_Indemnizatie]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[TipSpor] DROP CONSTRAINT [DF_Sporuri_Indemnizatie]
END
GO


IF NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[TipSpor]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[TipSpor](
		[IDTipSpor] [int] IDENTITY(1,1) NOT NULL,
		[Nume] [varchar](15) NULL,
		[Descriere] [varchar](100) NULL,
		[Valoare] [decimal](18, 2) NOT NULL,
		[Format] [char](1) NULL,
		[Utilizat] [int] NULL,
		[Ordonare] [int] NULL,
		[IntraIn] [varchar](2) NULL,
		[ConcediuOdihna] [bit] NOT NULL,
		[ConcediuBoala] [bit] NOT NULL,
		[Permanent] [bit] NOT NULL,
		[Activ] [bit] NOT NULL,
		[DataDefinire] [datetime] NOT NULL,
		[IdOpContabila] [int] NOT NULL,
		[ConcediuOreSuplimentare] [bit] NOT NULL,
		[BazaCalculSpor] [varchar](2) NULL,
	 CONSTRAINT [PK_TipSpor] PRIMARY KEY CLUSTERED 
	(
		[IDTipSpor] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]

	SET IDENTITY_INSERT [TipSpor] ON
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(1,'Sp. Vechime','Spor de vechime',0.00,'G',1,499,'BV',0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,'SB')
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(2,'Sp. Fidelitate','Spor fidelitate',0.00,'G',5,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(3,'Sp. Noapte','Spor de Noapte',25.00,'P',72,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(4,'Sp.sarbatori','Spor ore lucrate in zile de sarbatoare',100.00,'P',9,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(5,'Spor Preventie','Spor Preventie',11.00,'P',7,500,NULL,1,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(6,'Penalizare 5%','Penalizare 5%',-5.00,'P',2,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(7,'Penalizare 10%','Penalizare 10%',-10.00,'P',0,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(8,'Penalizare 20%','Penalizare 20%',-20.00,'P',0,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(9,'Spor o.s.100','Spor ore suplimentare 100%',100.00,'P',15,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(10,'Spor lb.str. 8','Spor limbi straine 8%',8.00,'P',0,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(11,'Spor lb.str. 10','Spor limbi straine 10%',10.00,'P',0,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(12,'Spor ges.5','Spor gestiune 5%',5.00,'P',0,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(13,'Spor ges.10','Spor gestiune 10%',10.00,'P',0,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(14,'Spor fct.supl.','Spor functie suplimentara',100.00,'S',1,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(15,'Spor wekend','Spor wekend',50.00,'P',8,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(16,'Ore stagn.75','Ore stagnare 75%',75.00,'P',0,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(17,'Spor mas-pod','Spor masinist pod',10.00,'P',2,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(18,'Spor stabilita','Spor de stabilitate',5.00,'P',0,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(19,'Spor s.neuro','Spor de solicitare neuro',3.00,'P',1,500,'SG',0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(21,'Salar merit','Salar merit',15.00,'P',2,500,'SB',0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,'SG')
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(22,'Indemn.dirig','Indemnizatie dirigentie',10.00,'P',1,500,'BV',0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(23,'Indemn.in.sp','Indemnizatie Inv.special',15.00,'P',1,500,'BV',0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(24,'Spor tit.stiin.','Spor titlu stiintific',15.00,'P',3,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(25,'Spor p.ped.','Spor practica pedagogica',25.00,'P',1,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(26,'Spor loc.izo.','Spor mediu rural/loc izolat',30.00,'P',1,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(27,'Spor c.peric.','Spor conditii periculoase',10.00,'P',1,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(28,'Spor c.grele','Spor conditii grele',10.00,'P',1,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(29,'Spor pred.s','Spor predare simultana',7.00,'P',1,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(30,'Indemn cond','Indemnizatie conducere',25.00,'F',4,500,'SG',0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,'SG')
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(33,'SP.Toxicita','Spor Toxicitate',15.00,'P',10,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(34,'Sp.Patrim10','Spor Patrimoniu10',10.00,'P',5,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(35,'Sp.Patrim15','Spor Patrimoniu15',15.00,'P',11,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(36,'Sp.CFP10','Spor CFP10',10.00,'P',1,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(37,'Sp.CFP15','Spor CFP15',15.00,'P',1,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(38,'Garzi50','Spor garzi 50',50.00,'P',-1,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[BazaCalculSpor])VALUES(39,'Garzi100','Spor garzi 100',200.00,'P',0,500,NULL,0,0,1,1,'Aug 25 2009 11:53:07:350AM',0,0,NULL)
	SET IDENTITY_INSERT [TipSpor] OFF
	
END
GO



IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Persoane' AND COLUMN_NAME = 'PlatesteCAS')
BEGIN
ALTER TABLE dbo.Persoane ADD
	PlatesteCAS bit NOT NULL DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Persoane' AND COLUMN_NAME = 'PlatesteCASS')
BEGIN
ALTER TABLE dbo.Persoane ADD
	PlatesteCASS bit NOT NULL DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Persoane' AND COLUMN_NAME = 'PlatesteSomaj')
BEGIN
ALTER TABLE dbo.Persoane ADD
	PlatesteSomaj bit NOT NULL DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Persoane' AND COLUMN_NAME = 'PlatesteImpozit')
BEGIN
ALTER TABLE dbo.Persoane ADD
	PlatesteImpozit bit NOT NULL DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Persoane' AND COLUMN_NAME = 'AreDeducere')
BEGIN
ALTER TABLE dbo.Persoane ADD
	AreDeducere bit NOT NULL DEFAULT 1
END
GO

IF  NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[AngajatiSalarizare]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[AngajatiSalarizare](
		[IDAngajatSalarizare] [int] IDENTITY(1,1) NOT NULL,
		[IdPersoana] [int] NOT NULL,
		[DeLaData] [datetime] NOT NULL,
		[Salar] [int] NOT NULL,
		[IDDepartament] [int] NOT NULL,
		[IdTipGrad] [int] NOT NULL,
		[OreNorma] [smallint] NOT NULL,
		[FractiuneNorma] [decimal](5, 2) NOT NULL,
		[PanaLaData] [datetime] NULL,
		[NormaBaza] [bit] NOT NULL,
	 CONSTRAINT [PK_EMPLOYEES_1__100] PRIMARY KEY CLUSTERED 
	(
		[IDAngajatSalarizare] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]



	ALTER TABLE [dbo].[AngajatiSalarizare]  WITH CHECK ADD  CONSTRAINT [FK_ANGAJATISalarizare_DEPARTAMENTE] FOREIGN KEY([IDDepartament])
	REFERENCES [dbo].[Departamente] ([IDDepartament])
	ON UPDATE CASCADE
	ON DELETE CASCADE

	ALTER TABLE [dbo].[AngajatiSalarizare] CHECK CONSTRAINT [FK_ANGAJATISalarizare_DEPARTAMENTE]
	ALTER TABLE [dbo].[AngajatiSalarizare]  WITH CHECK ADD  CONSTRAINT [FK_AngajatiSalarizare_Persoane] FOREIGN KEY([IdPersoana])
	REFERENCES [dbo].[Persoane] ([IdPersoana])
	ALTER TABLE [dbo].[AngajatiSalarizare] CHECK CONSTRAINT [FK_AngajatiSalarizare_Persoane]
	ALTER TABLE [dbo].[AngajatiSalarizare] ADD  CONSTRAINT [DF_AngajatiSalarizare_DeLaData]  DEFAULT (getdate()) FOR [DeLaData]
	ALTER TABLE [dbo].[AngajatiSalarizare] ADD  CONSTRAINT [DF_AngajatiSalarizare_IdTipGrad]  DEFAULT ((0)) FOR [IdTipGrad]
	ALTER TABLE [dbo].[AngajatiSalarizare] ADD  CONSTRAINT [DF_AngajatiSalarizare_OreNorma]  DEFAULT ((0)) FOR [OreNorma]
	ALTER TABLE [dbo].[AngajatiSalarizare] ADD  CONSTRAINT [DF_AngajatiSalarizare_FractiuneNorma]  DEFAULT ((1)) FOR [FractiuneNorma]
	ALTER TABLE [dbo].[AngajatiSalarizare] ADD  CONSTRAINT [DF_AngajatiSalarizare_NormaBaza]  DEFAULT ((0)) FOR [NormaBaza]
END
GO


/****** Object:  StoredProcedure [dbo].[resuma_CalculSpor]    Script Date: 01/30/2011 14:39:12 ******/
IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[resuma_CalculSpor]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[resuma_CalculSpor]
GO

CREATE PROCEDURE [dbo].[resuma_CalculSpor] 
	@Anul smallint,
	@Luna smallint,
	@CodUnitate smallint,
	@IDPersoanaSelectata int
AS

DECLARE @MesajEroare varchar(500)
DECLARE @prcVech    float
DECLARE @Salariu    DECIMAL(18,2)
DECLARE @Norma      integer
DECLARE @OreAcord   integer
DECLARE @OrePontate integer

DECLARE @IDPersoana int
DECLARE @PrcFctSupl decimal(5,2)
DECLARE @SumFctSupl decimal(18,2)
DECLARE @AreSpVech  char(1)
DECLARE @AreSpFctS  char(1)
DECLARE @IDDepartament int
declare @OP decimal (18,2)
declare @op_spor decimal(5,2)
declare @OPP integer
declare @IdTipSpor smallint
declare @valoare decimal(18,2)
declare @Format char(1)
declare @ConcediuOdihna char(1)
declare @c_boala char(1)
declare @c_ore_supl char(1)
declare @e_permanent char(1)
DECLARE @Salar_orar decimal(18,2)
DECLARE @Norma_redusa  integer
declare @ore_lucrate integer
DECLARE @valoare_spor decimal(18,2)
declare @valoare_spor_o decimal(18,2)
declare @valoare_spor_int decimal(18,2)
declare @ore_spor integer
declare @aa_vech integer
declare @baze varchar(2)
declare @dif integer
declare @ol integer

declare @ZI_COB integer
declare @ZI_NEMO integer
declare @ZI_OBL_PL integer
declare @ORE_COB integer
declare @OZI_OBL_PL integer
declare @OPS integer
declare @ZI_ODH integer
declare @OZI_ODH integer
declare @wore_l integer
declare @oo integer
declare @KACORD decimal(5,2)
declare @O_CFS integer
declare @ZI_OBL_NPL integer
declare @OZI_OBL_NPL integer
declare @ore_total integer
declare @pagina integer


/*==== VARIABILE PENTRU BUGETARI*/

declare @SalarCfGrilei decimal(18,2)
declare @SalarBaza decimal(18,2)
declare @BazaVech decimal(18,2)
declare @SporVechime decimal(18,2)
declare @baza_spor decimal(18,2)
DECLARE @OreNorma smallint
DECLARE @FractiuneNorma decimal(5,2)
DECLARE @IDAngajatSalarizare int
DECLARE @IDOcupatie int

select @op = ZileLucratoare from [resuma_SetariLuna] WHERE Luna=@Luna AND Anul=@Anul

IF @IDPersoanaSelectata=-1
	DELETE FROM  CalculSporuri where Luna=@Luna AND Anul=@Anul
ELSE
	DELETE FROM CalculSporuri WHERE [IDPersoana] = @IDPersoanaSelectata AND Luna=@Luna AND Anul=@Anul


-- ----------------------------------------- CALCUL SPORURI BUGETARI ---------------------------------------------


-- ************************ CAlCUL SPORURI BUGETARI CARE INTRA IN SALAR CF.GRILEI (intra_in='SG') ******************
DECLARE  crsSpor SCROLL CURSOR FOR 
SELECT DISTINCT 
	Ps.IDPersoana,Ans.IDAngajatSalarizare,Ans.IDDepartament,pss.IDTipSpor,Ans.Salar,
	ts.BazaCalculSpor,ts.valoare,ts.Format,ts.ConcediuOdihna,Ans.OreNorma,Ans.FractiuneNorma,AnO.IDOcupatie
	FROM  	
	    [Persoane] Ps 
	    JOIN [AngajatiSalarizare] Ans ON Ps.IdPersoana = Ans.IdPersoana
	    JOIN [AngajatiOcupatii] AnO ON Ps.IdPersoana = AnO.IdPersoana
	    JOIN [AngajatiSporuri] Pss ON Ps.IdPersoana = Pss.IdPersoana
		JoiN [TipSpor] ts ON Pss.IDTipSpor = ts.IDTipSpor
	WHERE   
	Ps.CodUnitate=@CodUnitate and ts.IntraIn='SG'  and ts.Format<>'G' AND
	YEAR(Pss.DeLaData)*100+MONTH(Pss.DeLaData)<=@Anul*100+@luna 
	AND (Pss.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Pss.PanaLaData)*100+MONTH(Pss.PanaLaData))
	AND (AnO.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(AnO.PanaLaData)*100+MONTH(AnO.PanaLaData))
	AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
	AND (Ps.CodUnitate=@CodUnitate) 
	AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata)


OPEN crsSpor
FETCH first from  crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie


WHILE @@fetch_status = 0  
BEGIN

  SELECT @ore_lucrate=isnull((select sum(Pt.Valoare)
  FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
  WHERE  
	Pt.IdPersoana = @IDPersoana AND 
   	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
   	Tp.TIP = 'P' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

  SELECT @ZI_ODH=isnull((select sum(pt.valoare)
  FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
  WHERE 
	Pt.IdPersoana = @IDPersoana AND 
	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
	Tp.TIP = 'O' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)
	
  IF @Format='F'		
	SELECT @valoare = Valoare FROM [GrileOcupatii] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie		
  IF @Format='D'		
	SELECT @valoare = Valoare FROM [GrileOcupatiiDep] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie AND [IDDepartament]=@IDDepartament

	
	IF @ConcediuOdihna=1 
	begin
		set @valoare_spor=@salariu*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))*@valoare/100
		set @valoare_spor_o=@salariu*(@ZI_ODH/(@op*@FractiuneNorma))*@valoare/100
	end
	ELSE
	begin
		set @valoare_spor=@salariu*@valoare/100
		set @valoare_spor_o=0
	end
		
	if @Format='S'
		IF @ConcediuOdihna=1 
		begin
			set @valoare_spor=@valoare*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))
			set @valoare_spor_o=@valoare*(@ZI_ODH/(@op*@FractiuneNorma))
		end
		ELSE
		begin
			set @valoare_spor=@valoare
			set @valoare_spor_o=0
		end
		


	SET @valoare_spor_int=@salariu*@valoare/100
	UPDATE AngajatiSporuri SET [Procent]=@valoare, [Valoare]=ROUND(@valoare_spor_int,0) WHERE
		IdPersoana=@IDPersoana AND [IDTipSpor]=@IdTipSpor

	INSERT  INTO CalculSporuri 
		(IdPersoana, IdTipSpor, VALOARE, VALOARECO,VALOAREINT,PROCENT, IDAngajatSalarizare,Anul,Luna) 
     	 VALUES
		(@IDPersoana, @IdTipSpor,round (@valoare_spor,0),round (@valoare_spor_o,0), round (@valoare_spor_int,0),
		(case when @Format='S' then 0
			else @valoare
			end),@IDAngajatSalarizare,@Anul,@Luna)

	FETCH NEXT from crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie


END
CLOSE crsSpor
DEALLOCATE crsSpor


-- ************************ CAlCUL SPORURI BUGETARI CARE INTRA IN SALAR DE BAZA (intra_in = 'SB') ******************

DECLARE  crsSpor SCROLL CURSOR FOR 
SELECT DISTINCT 
	Ps.IDPersoana,Ans.IDAngajatSalarizare,Ans.IDDepartament,pss.IDTipSpor,Ans.Salar,
	ts.BazaCalculSpor,ts.valoare,ts.Format,ts.ConcediuOdihna,Ans.OreNorma,Ans.FractiuneNorma,AnO.IDOcupatie
	FROM  	
	    [Persoane] Ps 
	    JOIN [AngajatiSalarizare] Ans ON Ps.IdPersoana = Ans.IdPersoana
	    JOIN [AngajatiOcupatii] AnO ON Ps.IdPersoana = AnO.IdPersoana
	    JOIN [AngajatiSporuri] Pss ON Ps.IdPersoana = Pss.IdPersoana
		JoiN [TipSpor] ts ON Pss.IDTipSpor = ts.IDTipSpor
	WHERE   
	Ps.CodUnitate=@CodUnitate and ts.IntraIn='SB'  and ts.Format<>'G' AND
	YEAR(Pss.DeLaData)*100+MONTH(Pss.DeLaData)<=@Anul*100+@luna 
	AND (Pss.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Pss.PanaLaData)*100+MONTH(Pss.PanaLaData))
	AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
	AND (Ps.CodUnitate=@CodUnitate)
	AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata)


OPEN crsSpor
FETCH first from  crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

WHILE @@fetch_status = 0  
BEGIN

	SELECT @ore_lucrate=isnull((select sum(Pt.Valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE  
	Pt.IdPersoana = @IDPersoana AND 
   	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
   	Tp.TIP = 'P' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	SELECT @ZI_ODH=isnull((select sum(pt.valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE 
	Pt.IdPersoana = @IDPersoana AND 
	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
	Tp.TIP = 'O' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	IF @baze='SG'
		select @SalarCfGrilei=@salariu+isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.IntraIn='SG'),0)	
	else
		if @baze='SI'
			select @SalarCfGrilei=@salariu
		else
			select @SalarCfGrilei=0 
			
	IF @Format='F'		
		SELECT @valoare = Valoare FROM [GrileOcupatii] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie		
	IF @Format='D'		
		SELECT @valoare = Valoare FROM [GrileOcupatiiDep] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie AND [IDDepartament]=@IDDepartament


	IF @ConcediuOdihna=1 
	begin
		set @valoare_spor=@SalarCfGrilei*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))*@valoare/100
		set @valoare_spor_o=@SalarCfGrilei*(@ZI_ODH/(@op*@FractiuneNorma))*@valoare/100
	end
	ELSE
	begin
		set @valoare_spor=@SalarCfGrilei*@valoare/100
		set @valoare_spor_o=0
	end
		
	if @Format='S'
		IF @ConcediuOdihna=1 
		begin
			set @valoare_spor=@valoare*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))
			set @valoare_spor_o=@valoare*(@ZI_ODH/(@op*@FractiuneNorma))
		end
		ELSE
		begin
			set @valoare_spor=@valoare
			set @valoare_spor_o=0
		end
		

	select @valoare_spor_int=@SalarCfGrilei*@valoare/100
	UPDATE AngajatiSporuri SET [Procent]=@valoare, [Valoare]=ROUND(@valoare_spor_int,0) WHERE
		IdPersoana=@IDPersoana AND [IDTipSpor]=@IdTipSpor

	INSERT  INTO CalculSporuri 
		(IdPersoana, IdTipSpor, VALOARE, VALOARECO,VALOAREINT,PROCENT, IDAngajatSalarizare,Anul,Luna) 
     	 VALUES
		(@IDPersoana, @IdTipSpor,round (@valoare_spor,0),round (@valoare_spor_o,0), round (@valoare_spor_int,0),
		(case when @Format='S' then 0 else @valoare end),@IDAngajatSalarizare,@Anul,@Luna)

	FETCH NEXT from crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

END
CLOSE crsSpor
DEALLOCATE crsSpor




-- ************************ CAlCUL SPORURI BUGETARI CARE INTRA IN BAZA VECHIME  (intra_in='BV') ******************

DECLARE  crsSpor SCROLL CURSOR FOR 
SELECT DISTINCT 
	Ps.IDPersoana,Ans.IDAngajatSalarizare,Ans.IDDepartament,pss.IDTipSpor,Ans.Salar,
	ts.BazaCalculSpor,ts.valoare,ts.Format,ts.ConcediuOdihna,Ans.OreNorma,Ans.FractiuneNorma,AnO.IDOcupatie
	FROM  	
	    [Persoane] Ps 
	    JOIN [AngajatiSalarizare] Ans ON Ps.IdPersoana = Ans.IdPersoana
	    JOIN [AngajatiOcupatii] AnO ON Ps.IdPersoana = AnO.IdPersoana
	    JOIN [AngajatiSporuri] Pss ON Ps.IdPersoana = Pss.IdPersoana
		JoiN [TipSpor] ts ON Pss.IDTipSpor = ts.IDTipSpor
	WHERE   
	Ps.CodUnitate=@CodUnitate and ts.IntraIn='BV'  and ts.Format<>'G' AND
	YEAR(Pss.DeLaData)*100+MONTH(Pss.DeLaData)<=@Anul*100+@luna 
	AND (Pss.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Pss.PanaLaData)*100+MONTH(Pss.PanaLaData))
	AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
	AND (Ps.CodUnitate=@CodUnitate)
	AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata)


OPEN crsSpor
FETCH first from  crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

WHILE @@fetch_status = 0  
BEGIN

	SELECT @ore_lucrate=isnull((select sum(Pt.Valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE  
	Pt.IdPersoana = @IDPersoana AND 
   	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
   	Tp.TIP = 'P' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	SELECT @ZI_ODH=isnull((select sum(pt.valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE 
	Pt.IdPersoana = @IDPersoana AND 
	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
	Tp.TIP = 'O' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)
	
	IF @Format='F'		
		SELECT @valoare = Valoare FROM [GrileOcupatii] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie		
	IF @Format='D'		
		SELECT @valoare = Valoare FROM [GrileOcupatiiDep] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie AND [IDDepartament]=@IDDepartament

	select @Baza_Spor =(case 
		when @baze='SI' then @salariu 
		when @baze = 'SG' then @salariu+isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SG'),0)	
		when @baze = 'SB' then @salariu+isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor 
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SG'),0)	+ isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SB'),0)	

		else	0
	end)	

	IF @ConcediuOdihna=1 
	begin
		set @valoare_spor=@Baza_Spor*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))*@valoare/100
		set @valoare_spor_o=@Baza_Spor*(@ZI_ODH/(@op*@FractiuneNorma))*@valoare/100
	end
	ELSE
	begin
		set @valoare_spor=@Baza_Spor*@valoare/100
		set @valoare_spor_o=0
	end
		
	if @Format='S'
		IF @ConcediuOdihna=1 
		begin
			set @valoare_spor=@valoare*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))
			set @valoare_spor_o=@valoare*(@ZI_ODH/(@op*@FractiuneNorma))
		end
		ELSE
		begin
			set @valoare_spor=@valoare
			set @valoare_spor_o=0
		end
		

	
	select @valoare_spor_int=@Baza_Spor*@valoare/100
	UPDATE AngajatiSporuri SET [Procent]=@valoare, [Valoare]=ROUND(@valoare_spor_int,0) WHERE
		IdPersoana=@IDPersoana AND [IDTipSpor]=@IdTipSpor

	INSERT  INTO CalculSporuri 
		(IdPersoana, IdTipSpor, VALOARE, VALOARECO,VALOAREINT,PROCENT, IDAngajatSalarizare,Anul,Luna) 
     	 VALUES
		(@IDPersoana, @IdTipSpor,round (@valoare_spor,0),round (@valoare_spor_o,0), round (@valoare_spor_int,0),
		(case when @Format='S' then 0 else @valoare end),@IDAngajatSalarizare,@Anul,@Luna)

	FETCH NEXT from crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

END
CLOSE crsSpor
DEALLOCATE crsSpor


-- ************************ CAlCUL SPORURI BUGETARI CARE NU INTRA NICIUNDE   (intra_in este null) ******************


DECLARE  crsSpor SCROLL CURSOR FOR 
SELECT DISTINCT 
	Ps.IDPersoana,Ans.IDAngajatSalarizare,Ans.IDDepartament,pss.IDTipSpor,Ans.Salar,
	ts.BazaCalculSpor,ts.valoare,ts.Format,ts.ConcediuOdihna,Ans.OreNorma,Ans.FractiuneNorma,AnO.IDOcupatie
	FROM  	
	    [Persoane] Ps 
	    JOIN [AngajatiSalarizare] Ans ON Ps.IdPersoana = Ans.IdPersoana
	    JOIN [AngajatiOcupatii] AnO ON Ps.IdPersoana = AnO.IdPersoana
	    JOIN [AngajatiSporuri] Pss ON Ps.IdPersoana = Pss.IdPersoana
		JoiN [TipSpor] ts ON Pss.IDTipSpor = ts.IDTipSpor
	WHERE   
	Ps.CodUnitate=@CodUnitate and ts.IntraIn is null  and ts.Format<>'G' AND
	YEAR(Pss.DeLaData)*100+MONTH(Pss.DeLaData)<=@Anul*100+@luna 
	AND (Pss.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Pss.PanaLaData)*100+MONTH(Pss.PanaLaData))
	AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
	AND (Ps.CodUnitate=@CodUnitate)
	AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata)


OPEN crsSpor
FETCH first from  crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

WHILE @@fetch_status = 0  
BEGIN

	SELECT @ore_lucrate=isnull((select sum(Pt.Valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE  
	Pt.IdPersoana = @IDPersoana AND 
   	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
   	Tp.TIP = 'P' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	SELECT @ZI_ODH=isnull((select sum(pt.valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE 
	Pt.IdPersoana = @IDPersoana AND 
	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
	tp.TIP = 'O' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	select @Baza_Spor =(case 
		when @baze='SI' then @salariu 
		when @baze = 'SG' then @salariu+isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SG'),0)	
		when @baze = 'SB' then @salariu+isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SG'),0)	+ isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SB'),0)	

		else	0
	end)	
	
	IF @Format='F'		
		SELECT @valoare = Valoare FROM [GrileOcupatii] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie		
	IF @Format='D'		
		SELECT @valoare = Valoare FROM [GrileOcupatiiDep] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie AND [IDDepartament]=@IDDepartament

IF @ConcediuOdihna=1 
	begin
		set @valoare_spor=@Baza_Spor*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))*@valoare/100
		set @valoare_spor_o=@Baza_Spor*(@ZI_ODH/(@op*@FractiuneNorma))*@valoare/100
	end
	ELSE
	begin
		set @valoare_spor=@Baza_Spor*@valoare/100
		set @valoare_spor_o=0
	end
		
	if @Format='S'
		IF @ConcediuOdihna=1 
		begin
			set @valoare_spor=@valoare*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))
			set @valoare_spor_o=@valoare*(@ZI_ODH/(@op*@FractiuneNorma))
		end
		ELSE
		begin
			set @valoare_spor=@valoare
			set @valoare_spor_o=0
		end


	select @valoare_spor_int=@Baza_Spor*@valoare/100
	UPDATE AngajatiSporuri SET [Procent]=@valoare, [Valoare]=ROUND(@valoare_spor_int,0) WHERE
		IdPersoana=@IDPersoana AND [IDTipSpor]=@IdTipSpor
	INSERT  INTO CalculSporuri 
		(IdPersoana, IdTipSpor, VALOARE, VALOARECO,VALOAREINT,PROCENT, IDAngajatSalarizare,Anul,Luna) 
     	 VALUES
		(@IDPersoana, @IdTipSpor,round (@valoare_spor,0),round (@valoare_spor_o,0), round (@valoare_spor_int,0),
		(case when @Format='S' then 0 else @valoare end),@IDAngajatSalarizare,@Anul,@Luna)

	FETCH NEXT from crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

END
CLOSE crsSpor
DEALLOCATE crsSpor



-- --------------------------------------- CALCUL SPOR VECHIME  BUGETARI --------------------------------------------------
--- Format = G spor vechime

DECLARE @datavechimemunca datetime
DECLARE @zilesuspCM int
DECLARE  crsSpor SCROLL CURSOR FOR 
    SELECT DISTINCT 
	Ps.IDPersoana,Ans.IDAngajatSalarizare,Ans.IDDepartament,pss.IDTipSpor,Ans.Salar,Ps.DataVechimeMunca,
	isnull((select sum(p.valoare) from AngajatiPontaj p JOIN TipPrezenta tp ON tp.IDTipPrezenta = p.IdTipPrezenta
	where p.IDPersoana=Ps.IDPersoana and tp.Tip='N'),0) as z_nem,
	isnull((SELECT SUM(abs(datediff(day,pscm.PanaLaData,pscm.DeLaData))) FROM [PersoaneSuspCM] pscm WHERE pscm.IDPersoana=Ps.IDPersoana),0) as zile_susp,
	ts.BazaCalculSpor,ts.valoare,ts.Format,ts.ConcediuOdihna,Ans.OreNorma,Ans.FractiuneNorma,AnO.IDOcupatie
	FROM  	
	    [Persoane] Ps 
	    JOIN [AngajatiSalarizare] Ans ON Ps.IdPersoana = Ans.IdPersoana
	    JOIN [AngajatiSporuri] Pss ON Ps.IdPersoana = Pss.IdPersoana
	    JOIN [AngajatiOcupatii] AnO ON Ps.IdPersoana = AnO.IdPersoana
		JoiN [TipSpor] ts ON Pss.IDTipSpor = ts.IDTipSpor
	WHERE   
	Ps.CodUnitate=@CodUnitate AND ts.Format='G' AND
	YEAR(Pss.DeLaData)*100+MONTH(Pss.DeLaData)<=@Anul*100+@luna 
	AND (Pss.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Pss.PanaLaData)*100+MONTH(Pss.PanaLaData))
	AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
	AND (Ps.CodUnitate=@CodUnitate)
	AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata)


OPEN crsSpor
FETCH first from  crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@datavechimemunca,@ZI_NEMO,@zilesuspCM,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie
DECLARE @ultimaziLuna datetime
SET @ultimaziLuna = DATEADD(DAY,-1,
						DATEADD(MONTH,1,
							CAST(CAST(@anul as varchar(4)) + '-' + CAST(@luna as varchar(2)) + '-01' as datetime)
						)
					)


WHILE @@fetch_status = 0  
BEGIN
	select @AA_VECH=DATEDIFF(year,DATEADD(day,@zilesuspCM,@datavechimemunca),@ultimaziLuna) 
	SET @prcvech=-1
	
	IF 	@aa_vech < 0
	BEGIN
		SET @MesajEroare = 'Vechime negativa adica data de la care se calculeaza vechimea este ulterioara lunii curente [10002] (transa = ' + 
			CAST(@AA_Vech as varchar(20)) + ' datavechime=' + CAST(@datavechimemunca as varchar(100)) + 
			',zile suspendare=' + CAST(@zilesuspCM as varchar(10)) +', idPersoana={'+CAST(@IDPersoana as varchar(20))+'})'
		GOTO EROARE2
	END
	
	SELECT @prcVech = Valoare/100 
		FROM Grile g
		WHERE 
			g.IDTipSpor = @IdTipSpor AND
			(Minim <= @AA_VECH AND  @AA_VECH < Maxim) OR (Minim <= @AA_VECH AND  Maxim IS NULL)
			
	IF 	@prcVech = -1
	BEGIN
		SET @MesajEroare = 'Grila de vechime nu a fost definite pentru toate transele [10001] (transa = ' + 
			CAST(@AA_Vech as varchar(20)) + ' datavechime=' + CAST(@datavechimemunca as varchar(100)) + 
			',zile suspendare=' + CAST(@zilesuspCM as varchar(10)) +', idPersoana='+CAST(@IDPersoana as varchar(20))+')'
		GOTO EROARE2
	END
			
    --PRINT('Vechime=' + CAST(@AA_VECH as varchar(2)))
	SELECT @ore_lucrate=isnull((select sum(Pt.Valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE  
	Pt.IdPersoana = @IDPersoana AND 
   	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
   	Tp.TIP = 'P' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	SELECT @ZI_ODH=isnull((select sum(pt.valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE 
	Pt.IdPersoana = @IDPersoana AND 
	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
	tp.TIP = 'O' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)
--aduna la baza vechime toate sporurile care intra in calculul vechimii
	Select @BazaVech=@salariu+
		isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SG'),0)	+		
		isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SB'),0)	
		/*+
		isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and
				ts.intrain='BV'),0)*/	
				
				
	--pondereaza sporul de vechime cu timpul efectiv lucrat
	Select @SporVechime = @BazaVech*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))*@prcVech
	Select @Valoare_spor_o=@BazaVech*(@ZI_ODH/(@op*@FractiuneNorma))*@prcVech
	Select @Valoare_spor_int=@BazaVech*@prcVech
	--seteaza sporul in personal spor
	UPDATE AngajatiSporuri SET [Procent]=@prcVech*100, [Valoare]=ROUND(@valoare_spor_int,0) WHERE
	IdPersoana=@IDPersoana AND [IDTipSpor]=@IdTipSpor
	
	if @ZI_NEMO<>0
			Select @SporVechime=0
	INSERT  INTO CalculSporuri (IdPersoana, IdTipSpor, VALOARE, VALOARECO,VALOAREINT,PROCENT, IDAngajatSalarizare,Anul,Luna) 
     	 VALUES
		(@IDPersoana, @IdTipSpor,
		 round (@SporVechime,0), round (@Valoare_spor_o,0),  round (@Valoare_spor_int,0),
		 @prcVech*100,@IDAngajatSalarizare,@Anul,@Luna)

	FETCH NEXT from crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@datavechimemunca,@ZI_NEMO,@zilesuspCM,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie
END
CLOSE crsSpor
DEALLOCATE crsSpor



GOTO Gata
EROARE:
	RAISERROR ('Eroare la recalcularea salariilor!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
EROARE2:
	RAISERROR (@MesajEroare,16,5)	
	GOTO Gata
	
Gata:


GO


/****** Object:  StoredProcedure [dbo].[resuma_CalculPontajSume]    Script Date: 01/30/2011 14:44:03 ******/
IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[resuma_CalculPontajSume]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[resuma_CalculPontajSume]
GO

CREATE PROCEDURE [dbo].[resuma_CalculPontajSume] 
	@Anul smallint,
	@Luna smallint,
	@CodUnitate smallint,
	@IDPersoanaSelectata int
AS
DECLARE @MesajEroare varchar(500)

-------------Adaosuri
INSERT INTO [AngajatiSume]
     ([IDPersoana],[IDAngajatSalarizare],[Suma],[TipSuma],[TotalOreLucrate],[Anul],[Luna])      
     SELECT Ps.IDPersoana,Ans.IDAngajatSalarizare,0,'AD',0,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiAdaosuri] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) AND AnP.Luna=@Luna AND Anp.Anul=@Anul
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiSume] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND IDAngajatSalarizare=Ans.IDAngajatSalarizare AND [TipSuma]='AD' 
				   AND Anp.Luna=APS.Luna AND Anp.Anul=APS.Anul)
	GROUP BY Ps.IDPersoana,Ans.IDAngajatSalarizare				   
	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare sporuri'
		GOTO EROARE2				   
	END	

INSERT INTO [AngajatiSume]
     ([IDPersoana],[IDAngajatSalarizare],[Suma],[TipSuma],[TotalOreLucrate],[Anul],[Luna])      
     SELECT Ps.IDPersoana,Ans.IDAngajatSalarizare,0,'S',0,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [CalculSporuri] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) AND AnP.Luna=@Luna AND Anp.Anul=@Anul
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiSume] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND IDAngajatSalarizare=Ans.IDAngajatSalarizare AND [TipSuma]='S'
				   AND Anp.Luna=APS.Luna AND Anp.Anul=APS.Anul)
	GROUP BY Ps.IDPersoana,Ans.IDAngajatSalarizare				   
	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare sporuri'
		GOTO EROARE2				   
	END	

---adauga pozitille pentru care nu exista pontaj
	INSERT INTO [AngajatiSume]
     ([IDPersoana],[IDAngajatSalarizare],[Suma],[TipSuma],[TotalOreLucrate],[Anul],[Luna])      
     SELECT Ps.IDPersoana,Ans.IDAngajatSalarizare,0,'P',0,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiPontaj] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     JOIN [TipPrezenta] Tp ON Tp.IDTipPrezenta = AnP.IDTipPrezenta
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) AND AnP.Luna=@Luna AND Anp.Anul=@Anul
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiSume] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND IDAngajatSalarizare=Ans.IDAngajatSalarizare AND [TipSuma]='P')
	GROUP BY  Ps.IDPersoana,Ans.IDAngajatSalarizare

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare pontaje'
		GOTO EROARE2				   
	END	
				 
--sterge pozitiile eliminate

DELETE FROM [AngajatiSume] WHERE [TipSuma] in ('P','S','AD') AND
	NOT EXISTS(SELECT * FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiPontaj] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     JOIN [TipPrezenta] Tp ON Tp.IDTipPrezenta = AnP.IDTipPrezenta
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) 
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND
		[AngajatiSume].[IDPersoana]= Ps.IdPersoana AND [AngajatiSume].IDAngajatSalarizare=Ans.IDAngajatSalarizare )


	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la stergere inexistente'
		GOTO EROARE2				   
	END	


UPDATE [AngajatiSume] SET 
   [Suma] = (SELECT Coalesce(SUM(ROUND(AnP.Valoare*Anp.SalarUnitar,0)),0)
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiPontaj] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare

     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) 
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		[AngajatiSume].IDPersoana = Ps.IdPersoana AND [AngajatiSume].IDAngajatSalarizare = Ans.IDAngajatSalarizare
		AND [AngajatiSume].Luna = Anp.Luna AND [AngajatiSume].Anul=Anp.Anul),
		
    [TotalOreLucrate] =(Select  Coalesce(SUM(Case WHEN Tp.TimpBaza='O' THEN ROUND(AnP.Valoare,0) 
     WHEN Tp.TimpBaza='Z' THEN ROUND(AnP.Valoare*AnS.OreNorma,0)
     WHEN Tp.TimpBaza='S' THEN ROUND(AnP.Valoare,0) END),0) FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiPontaj] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     JOIN [TipPrezenta] Tp ON Tp.IDTipPrezenta = AnP.IDTipPrezenta
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) 
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		[AngajatiSume].IDPersoana = Ps.IdPersoana AND [AngajatiSume].IDAngajatSalarizare = Ans.IDAngajatSalarizare 
		AND [AngajatiSume].Luna = Anp.Luna AND [AngajatiSume].Anul=Anp.Anul)
	WHERE [TipSuma]='P'	AND [Luna]=@Luna AND [Anul]=@Anul

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la update pontaje'
		GOTO EROARE2				   
	END	


UPDATE [AngajatiSume] SET 
   [Suma] = (SELECT Coalesce(SUM(Valoare),0)
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [CalculSporuri] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate)  AND AnP.Luna=@luna AND Anp.Anul=@anul
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
	    [AngajatiSume].IDPersoana = Ps.IdPersoana AND [AngajatiSume].IDAngajatSalarizare = Ans.IDAngajatSalarizare),
    [TotalOreLucrate] =0
	WHERE [TipSuma]='S'  AND [Luna]=@Luna AND [Anul]=@Anul

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la update sporuri'
		GOTO EROARE2				   
	END	
----Adaosuri
UPDATE [AngajatiSume] SET 
   [Suma] = (SELECT Coalesce(SUM(suma),0)
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [AngajatiAdaosuri] AnP ON Ps.IdPersoana = AnP.IdPersoana AND Anp.IdAngajatSalarizare = Ans.IDAngajatSalarizare
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate)  AND AnP.Luna=@luna AND Anp.Anul=@anul
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
	    [AngajatiSume].IDPersoana = Ps.IdPersoana AND [AngajatiSume].IDAngajatSalarizare = Ans.IDAngajatSalarizare),
    [TotalOreLucrate] =0
	WHERE [TipSuma]='AD'  AND [Luna]=@Luna AND [Anul]=@Anul

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la update sporuri'
		GOTO EROARE2				   
	END	


GOTO Gata
EROARE:
	RAISERROR ('Eroare la recalcularea salariilor!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
EROARE2:
	RAISERROR (@MesajEroare,16,5)	
	GOTO Gata
	
Gata:



GO

/****** Object:  StoredProcedure [dbo].[resuma_CalculStatPlata]    Script Date: 01/30/2011 14:44:56 ******/
IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[resuma_CalculStatPlata]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[resuma_CalculStatPlata]
GO

CREATE PROCEDURE [dbo].[resuma_CalculStatPlata] 
	@Anul smallint,
	@Luna smallint,
	@CodUnitate smallint,
	@IDPersoanaSelectata int
AS

DECLARE @MesajEroare varchar(500)

-----pozitii pe angajat si pozitie salarizare
	INSERT INTO [AngajatiStatPlata]
     ([IDPersoana],[IDAngajatSalarizare],[Suma],[IDTipStatPlata],[Anul],[Luna])      
     SELECT Ps.IDPersoana,Ans.IDAngajatSalarizare,0, TSp.IDTipStatPlata,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [TipStatPlata] TSp ON TSp.Cod IN ('BR')
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) 
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiStatPlata] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND IDAngajatSalarizare=Ans.IDAngajatSalarizare AND TSp.IDTipStatPlata = IDTipStatPlata)
	GROUP BY  Ps.IDPersoana,Ans.IDAngajatSalarizare,TSp.IDTipStatPlata

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare pontaje'
		GOTO EROARE2				   
	END	


-----pozitii pe angajat si pozitie salarizare
	INSERT INTO [AngajatiStatPlata]
     ([IDPersoana],[IDAngajatSalarizare],[Suma],[IDTipStatPlata],[Anul],[Luna])      
     SELECT Ps.IDPersoana,Ans.IDAngajatSalarizare,0, TSp.IDTipStatPlata,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [TipStatPlata] TSp ON TSp.Cod IN ('BRT')
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) AND Ans.NormaBaza=1
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiStatPlata] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND IDAngajatSalarizare=Ans.IDAngajatSalarizare AND TSp.IDTipStatPlata = IDTipStatPlata)
	 GROUP BY  Ps.IDPersoana,Ans.IDAngajatSalarizare,TSp.IDTipStatPlata 

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare pontaje'
		GOTO EROARE2				   
	END	

				 
--sterge pozitiile eliminate

DELETE FROM [AngajatiStatPlata] WHERE IDTipStatPlata in (SELECT IDTipStatPlata FROM [TipStatPlata] WHERE COD IN ('BR','BRT')) AND
	NOT EXISTS(SELECT * FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) 
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND
		[AngajatiStatPlata].[IDPersoana]= Ps.IdPersoana AND [AngajatiStatPlata].IDAngajatSalarizare=Ans.IDAngajatSalarizare )


	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la stergere inexistente'
		GOTO EROARE2				   
	END	



	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la update sporuri'
		GOTO EROARE2				   
	END	

UPDATE [AngajatiStatPlata] SET 
	[Suma] = (SELECT Coalesce(SUM(Suma),0) FROM [AngajatiSume] ASum WHERE [AngajatiStatPlata].IDPersoana=Asum.IDPersoana AND
		[AngajatiStatPlata].IDAngajatSalarizare = Asum.IDAngajatSalarizare AND [AngajatiStatPlata].Luna = ASum.Luna
		AND [AngajatiStatPlata].Anul = ASum.Anul)
FROM TipStatPlata tsp		
WHERE  Luna = @Luna AND Anul=@Anul AND [AngajatiStatPlata].IDTipStatPlata = tsp.IDTipStatPlata
AND tsp.COD IN ('BR') 

UPDATE [AngajatiStatPlata] SET 
	[Suma] = (SELECT Coalesce(SUM(Suma),0) FROM [AngajatiStatPlata] ASum JOIN [TipStatPlata] tsp ON tsp.IDTipStatPlata=ASum.IDTipStatPlata
	 WHERE [AngajatiStatPlata].IDPersoana=Asum.IDPersoana AND
		tsp.COD IN ('BR')  AND [AngajatiStatPlata].Luna = ASum.Luna
		AND [AngajatiStatPlata].Anul = ASum.Anul)
FROM TipStatPlata tsp		
WHERE  Luna = @Luna AND Anul=@Anul AND [AngajatiStatPlata].IDTipStatPlata = tsp.IDTipStatPlata
AND tsp.COD IN ('BRT') 




GOTO Gata
EROARE:
	RAISERROR ('Eroare la recalcularea salariilor!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
EROARE2:
	RAISERROR (@MesajEroare,16,5)	
	GOTO Gata
	
Gata:

GO


/****** Object:  StoredProcedure [dbo].[resuma_CalculCAS]    Script Date: 01/30/2011 14:45:33 ******/
IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[resuma_CalculCAS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[resuma_CalculCAS]
GO


CREATE PROCEDURE [dbo].[resuma_CalculCAS] 
	@Anul smallint,
	@Luna smallint,
	@CodUnitate smallint,
	@IDPersoanaSelectata int
AS

DECLARE @MesajEroare varchar(500)

-----pozitii pe angajat si pozitie salarizare
	INSERT INTO [AngajatiRetineri]
     ([IDPersoana],[valoare],[IDTipRetinere],[Anul],[Luna])      
     SELECT Ps.IDPersoana,0, TSp.IDTipRetinere,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [TipRetinere] TSp ON TSp.Nume IN ('CAS')
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) AND Ans.NormaBaza=1 AND Ps.PlatesteCAS = 1
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiRetineri] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND TSp.IDTipRetinere = IDTipRetinere)
	   GROUP BY  Ps.IDPersoana,Ans.IDAngajatSalarizare,TSp.IDTipRetinere 

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare Retinere CAS'
		GOTO EROARE2				   
	END	

				 
--sterge pozitiile eliminate

DELETE FROM [AngajatiRetineri] WHERE IDTipRetinere in (SELECT IDTipRetinere FROM [TipRetinere] WHERE Nume IN ('CAS')) AND
	NOT EXISTS(SELECT * FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) AND Ps.PlatesteCAS=1
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND
		[AngajatiRetineri].[IDPersoana]= Ps.IdPersoana )


	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la stergere inexistente retinere CAS'
		GOTO EROARE2				   
	END	

DECLARE @ProcentCAS decimal(18,12)
DECLARE @ZileLucratore decimal(15,2)
DECLARE @SalarMinim decimal(18,2)
DECLARE @SalarMediuBrut decimal(18,2)
DECLARE @NrSalCAS decimal(18,2)
DECLARE @CASMAX decimal(18,2)
DECLARE @PlafonareCAS bit


SELECT @ProcentCAS = ProcentCASAngajat,@SalarMinim = SalarMinim,@SalarMediuBrut=SalarMediubrut,
   @NrSalCAS = NrSalariiMaxCAS,@PlafonareCAS=PlafonareCAS  FROM ResUmaSetup WHERE [CodUnitate]=@CodUnitate
IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la setup'
		GOTO EROARE2				   
	END	   
SELECT @ZileLucratore=ZileLucratoare FROM resuma_SetariLuna WHERE [Luna]=@Luna AND [Anul]=@Anul
IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la setup'
		GOTO EROARE2				   
	END	   

SET @CASMAX = ROUND(@NrSalCAS*@SalarMediuBrut*@ProcentCAS/100,0)
PRINT(@CASMAX)
UPDATE [AngajatiRetineri] SET 
	[Valoare] = 
	---brutul total de pe departamentul cu norma de baza
	  ROUND(((SELECT SUM(Suma) FROM [AngajatiStatPlata] ASt JOIN [TipStatPlata] tsp ON Ast.IDTipStatPlata=tsp.IDTipStatPlata
	    WHERE [AngajatiRetineri].IDPersoana=Ast.IDPersoana AND
		[AngajatiRetineri].Luna = ASt.Luna
		AND [AngajatiRetineri].Anul = ASt.Anul AND Cod IN ('BRT'))
	--zilele boala de pe toate depatamentele
	  +(SELECT COALESCE(SUM(ZileBoala),0) FROM [AngajatiBoli] AnB WHERE [AngajatiRetineri].IDPersoana=AnB.IDPersoana AND
		 [AngajatiRetineri].Luna = AnB.Luna AND [AngajatiRetineri].Anul = AnB.Anul)*@SalarMinim/@ZileLucratore
	 --elimina pontajul pentru care nu se plateste CAS
	  -	 (SELECT COALESCE(SUM(Valoare*SalarUnitar),0) FROM [AngajatiPontaj] AnP JOIN [TipPrezenta] tp ON AnP.IdTipPrezenta = tp.IdTipPrezenta
	     WHERE [AngajatiRetineri].IDPersoana=AnP.IDPersoana AND tp.InclusCAS=0 AND
		 [AngajatiRetineri].Luna = AnP.Luna AND [AngajatiRetineri].Anul = AnP.Anul)
		--ELIMINA SPORURILE PENTRU CARE NU SE PLATESTE cas 
	   -(SELECT COALESCE(SUM(AnP.Valoare),0) FROM CalculSporuri AnP JOIN [TipSPOR] tp ON AnP.IdTipSpor = tp.IdTipSpor
	     WHERE [AngajatiRetineri].IDPersoana=AnP.IDPersoana AND tp.InclusCAS=0 AND
		 [AngajatiRetineri].Luna = AnP.Luna AND [AngajatiRetineri].Anul = AnP.Anul)
		--ELIMINA ADAOSURILE CARE NU SUNT INCLUSE IN cas
		-(SELECT COALESCE(SUM(AnP.Suma),0) FROM AngajatiADAOSURI AnP JOIN [TipAdaos] tp ON AnP.IdTipAdaos = tp.IdTipAdaos
	     WHERE [AngajatiRetineri].IDPersoana=AnP.IDPersoana AND tp.InclusCAS=0 AND
		 [AngajatiRetineri].Luna = AnP.Luna AND [AngajatiRetineri].Anul = AnP.Anul))* (@ProcentCAS/100)  ,0)
	
	FROM TipRetinere tsp 
	WHERE  Luna = @Luna AND Anul=@Anul AND tsp.NUME IN ('CAS') AND [AngajatiRetineri].IDTipRetinere = tsp.IDTipRetinere		

IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare CAS'
		GOTO EROARE2				   
	END	
	
IF @PlafonareCAS=1 AND @CASMAX>0
UPDATE [AngajatiRetineri] SET 
	[Valoare] = @CASMAX
FROM TipRetinere tsp		
	WHERE  Luna = @Luna AND Anul=@Anul AND [AngajatiRetineri].IDTipRetinere = tsp.IDTipRetinere
	AND tsp.NUME IN ('CAS') AND [Valoare]>@CASMAX

IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la limitare CAS'
		GOTO EROARE2				   
	END	

GOTO Gata
EROARE:
	RAISERROR ('Eroare la recalcularea salariilor!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
EROARE2:
	RAISERROR (@MesajEroare,16,5)	
	GOTO Gata
	
Gata:

GO





/****** Object:  StoredProcedure [dbo].[resuma_CalculSalarii]    Script Date: 01/30/2011 14:41:52 ******/
IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[resuma_CalculSalarii]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[resuma_CalculSalarii]
GO


CREATE PROCEDURE [dbo].[resuma_CalculSalarii] 
	@_Anul smallint,
	@_Luna smallint,
	@_CodUnitate smallint,
	@_IDPersoanaSelectata int
AS

EXEC resuma_CalculSpor @Luna=@_Luna,@Anul=@_Anul,@CodUnitate=@_CodUnitate,@IDPersoanaSelectata=@_IDPersoanaSelectata
IF @@ERROR<>0 GOTO EROARE

EXEC resuma_CalculPontajSume @Luna=@_Luna,@Anul=@_Anul,@CodUnitate=@_CodUnitate,@IDPersoanaSelectata=@_IDPersoanaSelectata
IF @@ERROR<>0 GOTO EROARE

EXEC resuma_CalculStatPlata @Luna=@_Luna,@Anul=@_Anul,@CodUnitate=@_CodUnitate,@IDPersoanaSelectata=@_IDPersoanaSelectata
IF @@ERROR<>0 GOTO EROARE

EXEC resuma_CalculCAS @Luna=@_Luna,@Anul=@_Anul,@CodUnitate=@_CodUnitate,@IDPersoanaSelectata=@_IDPersoanaSelectata
IF @@ERROR<>0 GOTO EROARE


GOTO Gata
EROARE:
	RAISERROR ('Eroare la recalcularea salariilor!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
Gata:

GO


IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[AngajatiSume]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[AngajatiSume](
		[IDPontajSuma] [int] IDENTITY(1,1) NOT NULL,
		[IDPersoana] [int] NOT NULL,
		[IDAngajatSalarizare] [int] NOT NULL,
		[Suma] [decimal](18, 2) NOT NULL,
		[TotalOreLucrate] [decimal](18, 2) NOT NULL,
		[TipSuma] [varchar](5) NOT NULL,
		[Anul] [smallint] NOT NULL,
		[Luna] [smallint] NOT NULL,
	 CONSTRAINT [PK_AngajatiPontajSume] PRIMARY KEY CLUSTERED 
	(
		[IDPontajSuma] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]


	ALTER TABLE [dbo].[AngajatiSume] ADD  CONSTRAINT [DF_AngajatiPontajSume_Suma]  DEFAULT ((0)) FOR [Suma]
	ALTER TABLE [dbo].[AngajatiSume] ADD  CONSTRAINT [DF_AngajatiPontajSume_TotalOreLucrate]  DEFAULT ((0)) FOR [TotalOreLucrate]
	ALTER TABLE [dbo].[AngajatiSume] ADD  CONSTRAINT [DF_AngajatiPontajSume_TipSuma]  DEFAULT ('X') FOR [TipSuma]
	ALTER TABLE [dbo].[AngajatiSume] ADD  CONSTRAINT [DF_AngajatiPontajSume_Anul]  DEFAULT ((2000)) FOR [Anul]
	ALTER TABLE [dbo].[AngajatiSume] ADD  CONSTRAINT [DF_AngajatiPontajSume_Luna]  DEFAULT ((1)) FOR [Luna]

END
GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[TipStatPlata]') AND type in (N'U'))
	DROP TABLE [dbo].[TipStatPlata]
GO

CREATE TABLE [dbo].[TipStatPlata](
	[IDTipStatPlata] [smallint] IDENTITY(1,1) NOT NULL,
	[Cod] [varchar](20) NOT NULL,
	[Denumire] [varchar](100) NOT NULL,
	[Ordine] [int] NOT NULL,
 CONSTRAINT [PK_TipStatPlata] PRIMARY KEY CLUSTERED 
(
	[IDTipStatPlata] ASC
)ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TipStatPlata] ADD  CONSTRAINT [DF_TipStatPlata_Ordine]  DEFAULT ((0)) FOR [Ordine]
GO

SET IDENTITY_INSERT [TipStatPlata] ON
INSERT INTO [TipStatPlata] ([IDTipStatPlata],[Cod],[Denumire],[Ordine])VALUES(1,'BR','Brut',1000)
INSERT INTO [TipStatPlata] ([IDTipStatPlata],[Cod],[Denumire],[Ordine])VALUES(2,'BRT','Total Brut',2000)
INSERT INTO [TipStatPlata] ([IDTipStatPlata],[Cod],[Denumire],[Ordine])VALUES(3,'VN','Venit Net',3000)
INSERT INTO [TipStatPlata] ([IDTipStatPlata],[Cod],[Denumire],[Ordine])VALUES(4,'DED','Deducere',4000)
INSERT INTO [TipStatPlata] ([IDTipStatPlata],[Cod],[Denumire],[Ordine])VALUES(5,'VBC','Venit Baza Calcul',5000)
INSERT INTO [TipStatPlata] ([IDTipStatPlata],[Cod],[Denumire],[Ordine])VALUES(6,'IMP','Impozit',6000)
INSERT INTO [TipStatPlata] ([IDTipStatPlata],[Cod],[Denumire],[Ordine])VALUES(7,'CN','Castig Net',7000)
INSERT INTO [TipStatPlata] ([IDTipStatPlata],[Cod],[Denumire],[Ordine])VALUES(8,'RP','Rest Plata',8000)
SET IDENTITY_INSERT [TipStatPlata] OFF


IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[AngajatiStatPlata]') AND type in (N'U'))
DROP TABLE [dbo].[AngajatiStatPlata]
GO

CREATE TABLE [dbo].[AngajatiStatPlata](
	[IDStatPlata] [int] IDENTITY(1,1) NOT NULL,
	[IDTipStatPlata] [smallint] NOT NULL,
	[IDPersoana] [int] NOT NULL,
	[IDAngajatSalarizare] [int] NOT NULL,
	[Suma] [decimal](18, 2) NOT NULL,
	[Anul] [smallint] NOT NULL,
	[Luna] [smallint] NOT NULL,
 CONSTRAINT [PK_AngajatiStatPlata] PRIMARY KEY CLUSTERED 
(
	[IDStatPlata] ASC
)ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AngajatiStatPlata] ADD  CONSTRAINT [DF_AngajatiStatPlata_Suma]  DEFAULT ((0)) FOR [Suma]
GO

ALTER TABLE [dbo].[AngajatiStatPlata] ADD  CONSTRAINT [DF_AngajatiStatPlata_Anul]  DEFAULT ((2000)) FOR [Anul]
GO

ALTER TABLE [dbo].[AngajatiStatPlata] ADD  CONSTRAINT [DF_AngajatiStatPlata_Luna]  DEFAULT ((1)) FOR [Luna]
GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[AngajatiRetineri]') AND type in (N'U'))
DROP TABLE [dbo].[AngajatiRetineri]
GO


CREATE TABLE [dbo].[AngajatiRetineri](
	[IDAngajatRetinere] [int] IDENTITY(1,1) NOT NULL,
	[IDPersoana] [int] NOT NULL,
	[IDTipRetinere] [int] NOT NULL,
	[Valoare] [decimal](18, 2) NOT NULL,
	[Luna] [smallint] NOT NULL,
	[Anul] [smallint] NOT NULL,
 CONSTRAINT [PK_AngajatiRetineri] PRIMARY KEY CLUSTERED 
(
	[IDAngajatRetinere] ASC
)ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AngajatiRetineri]  WITH CHECK ADD  CONSTRAINT [FK_AngajatiRetineri_Persoane] FOREIGN KEY([IDPersoana])
REFERENCES [dbo].[Persoane] ([IdPersoana])
GO

ALTER TABLE [dbo].[AngajatiRetineri] CHECK CONSTRAINT [FK_AngajatiRetineri_Persoane]
GO

ALTER TABLE [dbo].[AngajatiRetineri]  WITH CHECK ADD  CONSTRAINT [FK_AngajatiRetineri_TipRetinere] FOREIGN KEY([IDTipRetinere])
REFERENCES [dbo].[TipRetinere] ([IDTipRetinere])
GO

ALTER TABLE [dbo].[AngajatiRetineri] CHECK CONSTRAINT [FK_AngajatiRetineri_TipRetinere]
GO

ALTER TABLE [dbo].[AngajatiRetineri] ADD  CONSTRAINT [DF_AngajatiRetineri_Valoare]  DEFAULT ((0)) FOR [Valoare]
GO

ALTER TABLE [dbo].[AngajatiRetineri] ADD  CONSTRAINT [DF_AngajatiRetineri_Luna]  DEFAULT ((1)) FOR [Luna]
GO

ALTER TABLE [dbo].[AngajatiRetineri] ADD  CONSTRAINT [DF_AngajatiRetineri_Anul]  DEFAULT ((2000)) FOR [Anul]
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiBoli' AND COLUMN_NAME = 'ZileBoala')
BEGIN
ALTER TABLE dbo.AngajatiBoli ADD
	ZileBoala decimal(18,2) NOT NULL DEFAULT 0
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TipSpor' AND COLUMN_NAME = 'InclusCAS')
BEGIN
ALTER TABLE dbo.TipSpor ADD InclusCAS bit NOT NULL DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[ConcediiMedicale]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[ConcediiMedicale](
		[IdCM] [int] IDENTITY(1,1) NOT NULL,
		[IdPersoana] [int] NOT NULL,
		[NumarCM] [varchar](10) NOT NULL,
		[SerieCM] [varchar](10) NOT NULL,
		[DataCM] [datetime] NOT NULL,
		[DataInceput] [datetime] NOT NULL,
		[DataSfarsit] [datetime] NOT NULL,
		[IdCMContinuare] [int] NULL,
		[SumaCM] [decimal](18, 2) NOT NULL,
		[BazaCM] [decimal](18, 5) NOT NULL,
		[IdDepartament] [int] NOT NULL,
		[CodUrgenta] [varchar](20) NULL,
		[CodBoalaContagioasa] [varchar](20) NULL,
		[CodDiagnostic] [varchar](20) NULL,
		[ZileBazaCM] [int] NOT NULL,
		[ZileCAS] [int] NOT NULL,
		[ZileFAMBP] [int] NOT NULL,
		[IDTipBoala] [smallint] NOT NULL,
		[CNPCopil] [varchar](13) NULL,
		[LocPrescriere] [varchar](2) NULL,
		[FaraPlataIndemnizatie] [bit] NULL,
		[AvizITM_DSP] [bit] NULL,
		[BazaCalculataIncorect] [bit] NULL,
	 CONSTRAINT [PK_ConcediiMedicale] PRIMARY KEY CLUSTERED 
	(
		[IdCM] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]


	ALTER TABLE [dbo].[ConcediiMedicale]  WITH CHECK ADD  CONSTRAINT [FK_ConcediiMedicale_ConcediiMedicale] FOREIGN KEY([IdCMContinuare])
	REFERENCES [dbo].[ConcediiMedicale] ([IdCM])
	ALTER TABLE [dbo].[ConcediiMedicale] CHECK CONSTRAINT [FK_ConcediiMedicale_ConcediiMedicale]
	ALTER TABLE [dbo].[ConcediiMedicale]  WITH CHECK ADD  CONSTRAINT [FK_ConcediiMedicale_Departamente] FOREIGN KEY([IdDepartament])
	REFERENCES [dbo].[Departamente] ([IDDepartament])
	ALTER TABLE [dbo].[ConcediiMedicale] CHECK CONSTRAINT [FK_ConcediiMedicale_Departamente]
	ALTER TABLE [dbo].[ConcediiMedicale]  WITH CHECK ADD  CONSTRAINT [FK_ConcediiMedicale_Persoane] FOREIGN KEY([IdPersoana])
	REFERENCES [dbo].[Persoane] ([IdPersoana])
	ALTER TABLE [dbo].[ConcediiMedicale] CHECK CONSTRAINT [FK_ConcediiMedicale_Persoane]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_IdAngajat]  DEFAULT ((0)) FOR [IdPersoana]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_NumarCM]  DEFAULT ('') FOR [NumarCM]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_SerieCM]  DEFAULT ('') FOR [SerieCM]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_DataCM]  DEFAULT (getdate()) FOR [DataCM]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_DataInceput]  DEFAULT (getdate()) FOR [DataInceput]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_DataSfarsit]  DEFAULT (getdate()) FOR [DataSfarsit]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_IdCMContinuare]  DEFAULT ((0)) FOR [IdCMContinuare]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_SumaCM]  DEFAULT ((0)) FOR [SumaCM]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_BazaCM]  DEFAULT ((0)) FOR [BazaCM]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_IdDepartament]  DEFAULT ((0)) FOR [IdDepartament]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_ZileCAS]  DEFAULT ((0)) FOR [ZileBazaCM]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_ZileBoalaCumulate]  DEFAULT ((0)) FOR [ZileCAS]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_ZileFAMBP]  DEFAULT ((0)) FOR [ZileFAMBP]
	ALTER TABLE [dbo].[ConcediiMedicale] ADD  CONSTRAINT [DF_ConcediiMedicale_IDTipBoala]  DEFAULT ((1)) FOR [IDTipBoala]

END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSalarizare' AND COLUMN_NAME = 'ServiciiPeChitanta')
BEGIN
ALTER TABLE dbo.AngajatiSalarizare ADD ServiciiPeChitanta bit NULL
END
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Chitanta' AND COLUMN_NAME = 'IdAngajat')
BEGIN
	EXECUTE sp_rename N'dbo.Chitanta.IdAngajat', N'IdPersoana', 'COLUMN' 
END
GO

IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[FK_Chitanta_Persoane]') AND type in  (N'F'))
BEGIN
	DELETE FROM [Chitanta]
	ALTER TABLE [dbo].[Chitanta]  WITH CHECK ADD  CONSTRAINT [FK_Chitanta_Persoane] FOREIGN KEY([IdPersoana])
	REFERENCES [dbo].[Persoane] ([IdPersoana])
	ALTER TABLE [dbo].[Chitanta] CHECK CONSTRAINT [FK_Chitanta_Persoane]
END
GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[sankit_PersonalMedical]'))
DROP VIEW [dbo].[sankit_PersonalMedical]
GO

CREATE VIEW [dbo].[sankit_PersonalMedical]
AS
SELECT     dbo.Persoane.NumePrenume, dbo.Persoane.IdPersoana
FROM         dbo.Persoane INNER JOIN
                      dbo.AngajatiSalarizare ON dbo.Persoane.IdPersoana = dbo.AngajatiSalarizare.IdPersoana
WHERE     (dbo.AngajatiSalarizare.ServiciiPeChitanta = 1)

GO

IF  NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[SanKitSetup]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[SanKitSetup](
		[PrimaryKey] [smallint] NOT NULL,
		[LunaCurenta] [smallint] NOT NULL,
		[AnulCurent] [smallint] NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
		[IDSerieChitanta] [varchar](20) NULL,
	 CONSTRAINT [PK_SanKitSetup] PRIMARY KEY CLUSTERED 
	(
		[PrimaryKey] ASC,
		[CodUnitate] ASC
	) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[SanKitSetup]  WITH CHECK ADD  CONSTRAINT [FK_SanKitSetup_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])
	ALTER TABLE [dbo].[SanKitSetup] CHECK CONSTRAINT [FK_SanKitSetup_Unitati]
	ALTER TABLE [dbo].[SanKitSetup] ADD  CONSTRAINT [DF_SanKitSetup_PrimaryKey]  DEFAULT ((0)) FOR [PrimaryKey]
	ALTER TABLE [dbo].[SanKitSetup] ADD  CONSTRAINT [DF_SanKitSetup_LunaCurenta]  DEFAULT ((0)) FOR [LunaCurenta]
	ALTER TABLE [dbo].[SanKitSetup] ADD  CONSTRAINT [DF_SanKitSetup_AnulCurent]  DEFAULT ((0)) FOR [AnulCurent]
	ALTER TABLE [dbo].[SanKitSetup] ADD  CONSTRAINT [DF_SanKitSetup_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]
END
GO



IF NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[Beneficiar]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[Beneficiar](
		[IDBeneficiar] [int] IDENTITY(1,1) NOT NULL,
		[Nume] [nvarchar](100) NOT NULL,
		[Adresa] [nvarchar](100) NOT NULL,
	 CONSTRAINT [PK_Beneficiar] PRIMARY KEY CLUSTERED 
	(
		[IDBeneficiar] ASC
	) ON [PRIMARY]
	) ON [PRIMARY]
END
GO


IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[Chitanta]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[Chitanta](
		[IDChitanta] [int] IDENTITY(1,1) NOT NULL,
		[Numar] [varchar](50) NOT NULL,
		[Data] [datetime] NOT NULL,
		[IdPersoanaServiciu] [int] NOT NULL,
		[IdBeneficiar] [int] NOT NULL,
		[ValoareTotala] [decimal](18, 2) NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
		[UserId] [uniqueidentifier] NULL,
	 CONSTRAINT [PK_Chitanta] PRIMARY KEY CLUSTERED 
	(
		[IDChitanta] ASC
	) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[Chitanta]  WITH CHECK ADD  CONSTRAINT [FK_Chitanta_Beneficiar] FOREIGN KEY([IdBeneficiar])
	REFERENCES [dbo].[Beneficiar] ([IDBeneficiar])
	ALTER TABLE [dbo].[Chitanta] CHECK CONSTRAINT [FK_Chitanta_Beneficiar]
	ALTER TABLE [dbo].[Chitanta]  WITH CHECK ADD  CONSTRAINT [FK_Chitanta_Persoane] FOREIGN KEY([IdPersoanaServiciu])
	REFERENCES [dbo].[Persoane] ([IdPersoana])
	ALTER TABLE [dbo].[Chitanta] CHECK CONSTRAINT [FK_Chitanta_Persoane]
	ALTER TABLE [dbo].[Chitanta]  WITH CHECK ADD  CONSTRAINT [FK_Chitanta_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])
	ALTER TABLE [dbo].[Chitanta] CHECK CONSTRAINT [FK_Chitanta_Unitati]
	ALTER TABLE [dbo].[Chitanta] ADD  CONSTRAINT [DF_Chitanta_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]
END
GO

/****** Object:  Table [dbo].[TipActeMedicale]    Script Date: 02/03/2011 18:53:53 ******/
IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[TipActeMedicale]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[TipActeMedicale](
		[IdTipActMedical] [int] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](50) NOT NULL,
		[IdParinte] [int] NULL,
	 CONSTRAINT [PK_TipActeMedicale] PRIMARY KEY CLUSTERED 
	(
		[IdTipActMedical] ASC
	) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[TipActeMedicale]  WITH CHECK ADD  CONSTRAINT [FK_TipActeMedicale_TipActeMedicale] FOREIGN KEY([IdParinte])
	REFERENCES [dbo].[TipActeMedicale] ([IdTipActMedical])

	ALTER TABLE [dbo].[TipActeMedicale] CHECK CONSTRAINT [FK_TipActeMedicale_TipActeMedicale]

	SET IDENTITY_INSERT [TipActeMedicale] ON
	INSERT INTO [TipActeMedicale] ([IdTipActMedical],[Denumire],[IdParinte])VALUES(1,'Clase',NULL)
	INSERT INTO [TipActeMedicale] ([IdTipActMedical],[Denumire],[IdParinte])VALUES(2,'Consultatie',1)
	INSERT INTO [TipActeMedicale] ([IdTipActMedical],[Denumire],[IdParinte])VALUES(3,'Fizioterapie',1)
	SET IDENTITY_INSERT [TipActeMedicale] OFF
END
GO



/****** Object:  Table [dbo].[ActeMedicale]    Script Date: 02/03/2011 18:57:32 ******/
IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[ActeMedicale]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[ActeMedicale](
		[IdActMedical] [int] IDENTITY(1,1) NOT NULL,
		[Cod] [varchar](10) NOT NULL,
		[Denumire] [varchar](100) NOT NULL,
		[Valoare] [decimal](18, 2) NOT NULL,
		[IdTipActMedical] [int] NOT NULL,
	 CONSTRAINT [PK_ActeMedicale] PRIMARY KEY CLUSTERED 
	(
		[IdActMedical] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[ActeMedicale]  WITH CHECK ADD  CONSTRAINT [FK_ActeMedicale_TipActeMedicale] FOREIGN KEY([IdTipActMedical])
	REFERENCES [dbo].[TipActeMedicale] ([IdTipActMedical])
	ALTER TABLE [dbo].[ActeMedicale] CHECK CONSTRAINT [FK_ActeMedicale_TipActeMedicale]

	SET IDENTITY_INSERT [ActeMedicale] ON
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(1,'mpci','Medic Primar CI',20.00,2)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(2,'mpcc','Medic Primar CC',15.00,2)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(3,'gal','Galvanizare',3.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(4,'ion','Ionizare',3.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(5,'cdd','Curenti Diadinamici',3.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(6,'ct','Curenti Trabet',3.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(7,'ci','Curenti Interferentiali',3.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(8,'rus','Unde Scurte',3.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(9,'mgp','Mega Pulse',4.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(10,'us','ctUltrasunet',3.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(11,'mdf','Magnetodiaflux',3.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(12,'tl','Terapie Laser',3.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(13,'s','Sollux',3.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(14,'ce','Curent Exponential',3.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(15,'a','Aerosoli',4.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(16,'m','Masaj',3.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(17,'p','Parafina',3.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(18,'kti','Kinetoterapie ind',6.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(19,'t','Tens',3.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(20,'cr','Curenti Rusesti',3.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(21,'itm','ITM',6.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(22,'iia','IIA',10.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(23,'ma','Manipulari articulare',3.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(24,'tv','Tractiuni vertebrale',4.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(25,'bg','Bai Galvanice',4.50,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(26,'ost','Osteodensiometrie',30.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(27,'osc','Oscilometrie',8.00,3)
	INSERT INTO [ActeMedicale] ([IdActMedical],[Cod],[Denumire],[Valoare],[IdTipActMedical])VALUES(28,'bb','Bai cu bule',8.00,3)
	SET IDENTITY_INSERT [ActeMedicale] OFF

END 
GO


IF not  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[ChitantaDetaliu]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[ChitantaDetaliu](
		[IdChitantaDetaliu] [int] IDENTITY(1,1) NOT NULL,
		[IdChitanta] [int] NOT NULL,
		[IdActMedical] [int] NOT NULL,
		[Cantitate] [decimal](18, 2) NOT NULL,
		[ValoareUnitara] [decimal](18, 2) NOT NULL,
		[Valoare] [decimal](18, 0) NOT NULL,
	 CONSTRAINT [PK_ChitantaDetaliu] PRIMARY KEY CLUSTERED 
	(
		[IdChitantaDetaliu] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[ChitantaDetaliu]  WITH CHECK ADD  CONSTRAINT [FK_ChitantaDetaliu_ActeMedicale] FOREIGN KEY([IdActMedical])
	REFERENCES [dbo].[ActeMedicale] ([IdActMedical])
	ALTER TABLE [dbo].[ChitantaDetaliu] CHECK CONSTRAINT [FK_ChitantaDetaliu_ActeMedicale]
	ALTER TABLE [dbo].[ChitantaDetaliu]  WITH CHECK ADD  CONSTRAINT [FK_ChitantaDetaliu_Chitanta] FOREIGN KEY([IdChitanta])
	REFERENCES [dbo].[Chitanta] ([IDChitanta])
	ALTER TABLE [dbo].[ChitantaDetaliu] CHECK CONSTRAINT [FK_ChitantaDetaliu_Chitanta]
END
GO


IF NOT EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[SeriiNumericeRezervare]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[SeriiNumericeRezervare](
		[IdSNRezervare] [int] IDENTITY(1,1) NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
		[IdSerie] [nvarchar](20) NOT NULL,
		[Data] [datetime] NOT NULL,
		[UserID] [uniqueidentifier] NOT NULL,
	 CONSTRAINT [PK_SeriiNumericeRezervare] PRIMARY KEY CLUSTERED 
	(
		[IdSNRezervare] ASC,
		[CodUnitate] ASC
	)ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[SeriiNumericeRezervare]  WITH CHECK ADD  CONSTRAINT [FK_SeriiNumericeRezervare_SeriiNumerice] FOREIGN KEY([IdSerie], [CodUnitate])
	REFERENCES [dbo].[SeriiNumerice] ([IdSerie], [CodUnitate])
	ALTER TABLE [dbo].[SeriiNumericeRezervare] CHECK CONSTRAINT [FK_SeriiNumericeRezervare_SeriiNumerice]
	ALTER TABLE [dbo].[SeriiNumericeRezervare]  WITH CHECK ADD  CONSTRAINT [FK_SeriiNumericeRezervare_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])
	ALTER TABLE [dbo].[SeriiNumericeRezervare] CHECK CONSTRAINT [FK_SeriiNumericeRezervare_Unitati]
	ALTER TABLE [dbo].[SeriiNumericeRezervare] ADD  CONSTRAINT [DF_SeriiNumericeRezervare_Data]  DEFAULT (getdate()) FOR [Data]
END
GO


IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[CreazaArhivaFares]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[CreazaArhivaFares]
GO

CREATE PROCEDURE [dbo].[CreazaArhivaFares]
	@An smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @strTabelaAn varchar(40)
	DECLARE @Comanda varchar(8000)
	DECLARE @existTable bit
	
	SET @strTabelaAn = cast(@An AS varchar(4)) 
	SET @existTable = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE 'Documente' + @strTabelaAn)
		SET @existTable = 1
	
	--verifica existenta tabeleli arhiva
	SET @Comanda = 'CREATE TABLE [dbo].[Documente'+@strTabelaAn+'](
		[IDDocument] [bigint] NOT NULL,
		[NrDoc] [varchar](20) NOT NULL,
		[DataDoc] [datetime] NOT NULL,
		[IDPartener] [bigint] NOT NULL,
		[CodUnitate] [smallint] NOT NULL DEFAULT 0,
		[CotaTVA] [decimal](5, 2) NOT NULL DEFAULT 0,
		[IDTipDocument] [smallint] NOT NULL DEFAULT 0,
		[TotalValoare] [decimal](25, 5) NOT NULL DEFAULT 0,
		[TotalValoareTVA] [decimal](25, 5) NOT NULL DEFAULT 0,
		[Total] [decimal](25, 5) NOT NULL DEFAULT 0,
		[IDRegimFiscal] [smallint] NOT NULL DEFAULT 0,
		[UserId] [uniqueidentifier] NULL,
		[IDDepartament] [int] NULL DEFAULT 0,
		[NrContract] [varchar](50) NULL,
		[DataContract] [datetime] NULL,
		[Observatii] [varchar](250) NULL,
		[DataScadenta] [datetime] NULL,
		[ValoareTotalaCuAdaos] [decimal](25, 5) NOT NULL DEFAULT 0,
		[NrDocReceptie] [varchar](20) NULL,
		[DataDocReceptie] [datetime] NULL,
	 CONSTRAINT [PK_Documente'+@strTabelaAn+'] PRIMARY KEY CLUSTERED 
	(
		[IDDocument] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	 CONSTRAINT [Dublare_Documente_Furnizor_Numar_Data'+@strTabelaAn+'] UNIQUE NONCLUSTERED 
	(
		[IDPartener] ASC,
		[CodUnitate] ASC,
		[NrDoc] ASC,
		[DataDoc] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	 CONSTRAINT [FK_Documente_Departamente'+@strTabelaAn+'] FOREIGN KEY ([IDDepartament]) REFERENCES [dbo].[Departamente] ([IDDepartament]),
	 CONSTRAINT [FK_Documente_Parteneri'+@strTabelaAn+'] FOREIGN KEY([IDPartener]) REFERENCES [dbo].[Parteneri] ([IDPartener]),
	 CONSTRAINT [FK_Documente_RegimFiscal'+@strTabelaAn+'] FOREIGN KEY([IDRegimFiscal]) REFERENCES [dbo].[RegimFiscal] ([IDRegimFiscal]),
	 CONSTRAINT [FK_Documente_TipDocument'+@strTabelaAn+'] FOREIGN KEY([IDTipDocument]) REFERENCES [dbo].[TipDocument] ([IDTipDocument]),
	 CONSTRAINT [FK_Documente_Unitati'+@strTabelaAn+'] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	) ON [PRIMARY]'
		
	IF @existTable = 0
		EXEC(@Comanda)
		

	SET @existTable = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE 'DocumenteDetaliu'+@strTabelaAn)
		SET @existTable = 1

	SET @Comanda = 'CREATE TABLE [dbo].[DocumenteDetaliu'+@strTabelaAn+'](
		[IDDocumentDetaliu] [bigint] NOT NULL,
		[IDDocument] [bigint] NOT NULL,
		[IDArticol] [int] NOT NULL,
		[Cantitate] [decimal](25, 5) NOT NULL DEFAULT 0,
		[ValoareUnitara] [decimal](25, 5) NOT NULL  DEFAULT 0,
		[ValoareTVA] [decimal](25, 5) NOT NULL  DEFAULT 0,
		[ValoareTotala] [decimal](25, 5) NOT NULL,
		[CodUnitate] [smallint] NOT NULL  DEFAULT 0,
		[IDUnitateMasura] [smallint] NOT NULL  DEFAULT 0,
		[IDRegimFiscal] [smallint] NOT NULL  DEFAULT 0,
		[CotaTVA] [decimal](5, 2) NOT NULL DEFAULT 0,
		[TermenLivrare] [datetime] NULL,
		[IDVariantaArticol] [int] NULL,
		[Observatii] [varchar](250) NULL,
		[ValoareTotalaCuAdaos] [decimal](25, 5) NOT NULL DEFAULT 0,
	 CONSTRAINT [PK_DocumenteDetaliu'+@strTabelaAn+'] PRIMARY KEY CLUSTERED 
	(
		[IDDocumentDetaliu] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	CONSTRAINT [FK_DocumenteDetaliu_Articole'+@strTabelaAn+'] FOREIGN KEY([IDArticol]) REFERENCES [dbo].[Articole] ([IDArticol]),
	CONSTRAINT [FK_DocumenteDetaliu_ArticoleVariante'+@strTabelaAn+'] FOREIGN KEY([IDVariantaArticol])REFERENCES [dbo].[ArticoleVariante] ([IDVariantaArticol]),
	CONSTRAINT [FK_DocumenteDetaliu_Documente'+@strTabelaAn+'] FOREIGN KEY([IDDocument]) REFERENCES [dbo].[Documente'+@strTabelaAn+'] ([IDDocument]),
	CONSTRAINT [FK_DocumenteDetaliu_RegimFiscal'+@strTabelaAn+'] FOREIGN KEY([IDRegimFiscal]) REFERENCES [dbo].[RegimFiscal] ([IDRegimFiscal]),
	CONSTRAINT [FK_DocumenteDetaliu_Unitati'+@strTabelaAn+'] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [FK_DocumenteDetaliu_UnitatiMasura'+@strTabelaAn+'] FOREIGN KEY([IDUnitateMasura]) REFERENCES [dbo].[UnitatiMasura] ([IDUnitateMasura])
	) ON [PRIMARY]'
	
	IF @existTable = 0
		EXEC(@Comanda)
		
	SET @existTable = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE 'DocumentePlata'+@strTabelaAn)
		SET @existTable = 1

	SET @Comanda = 'CREATE TABLE [dbo].[DocumentePlata'+@strTabelaAn+'](
		[IDDocumentPlata] [int] NOT NULL,
		[Numar] [varchar](20) NOT NULL,
		[Data] [datetime] NOT NULL,
		[IDTipPlata] [smallint] NOT NULL,
		[ValoareTotala] [decimal](18, 2) NOT NULL DEFAULT 0,
		[IDPartener] [bigint] NOT NULL,
		[Reprezentand] [varchar](250) NULL,
		[TipOperatiune] [varchar](10) NOT NULL DEFAULT (''INC''),
		[CodUnitate] [smallint] NOT NULL DEFAULT 0,
		[DataExtras] [datetime] NULL,
	 CONSTRAINT [PK_DocumentePlata'+@strTabelaAn+'] PRIMARY KEY CLUSTERED 
	(
		[IDDocumentPlata] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	CONSTRAINT [FK_DocumentePlata'+@strTabelaAn+'_Parteneri] FOREIGN KEY([IDPartener]) REFERENCES [dbo].[Parteneri] ([IDPartener]),
	CONSTRAINT [FK_DocumentePlata'+@strTabelaAn+'_TipDocumentPlata] FOREIGN KEY([IDTipPlata]) REFERENCES [dbo].[TipDocumentPlata] ([IDTipPlata]),
	CONSTRAINT [FK_DocumentePlata'+@strTabelaAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	) ON [PRIMARY]'

	IF @existTable = 0
		EXEC(@Comanda)
		
	SET @existTable = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE 'DocumentePlataDetaliu'+@strTabelaAn)
		SET @existTable = 1

	SET @Comanda = 'CREATE TABLE [dbo].[DocumentePlataDetaliu'+@strTabelaAn+'](
		[IDDocPlataDetaliu] [int] NOT NULL,
		[IDDocument] [bigint] NULL,
		[IDDocumentPlata] [int] NOT NULL,
		[Valoare] [decimal](18, 2) NOT NULL DEFAULT 0,
	 CONSTRAINT [PK_DocumentePlataDetaliu'+@strTabelaAn+'] PRIMARY KEY CLUSTERED 
	(
		[IDDocPlataDetaliu] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
		CONSTRAINT [FK_DocumentePlataDetaliu'+@strTabelaAn+'_DocumentePlata'+@strTabelaAn+'] FOREIGN KEY([IDDocumentPlata]) REFERENCES [dbo].[DocumentePlata'+@strTabelaAn+'] ([IDDocumentPlata]),
		CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlataDetaliu'+@strTabelaAn+'] FOREIGN KEY([IDDocument]) REFERENCES [dbo].[Documente'+@strTabelaAn+'] ([IDDocument])	
	) ON [PRIMARY]'

	IF @existTable = 0
			EXEC(@Comanda)
			
	SET @existTable = 0
	IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[fares_DocumentePlatite'+@strTabelaAn+']'))
		SET @existTable = 1

	SET @Comanda = 'CREATE VIEW [dbo].[fares_DocumentePlatite'+@strTabelaAn+']
			AS
	SELECT     a.IDDocPlataDetaliu, a.IDDocumentPlata, a.Valoare, 
						  b.NrDoc + ''/'' + CONVERT(varchar(20), b.DataDoc, 101) AS NrDataDoc, a.IDDocument
	FROM         dbo.DocumentePlataDetaliu'+@strTabelaAn+' a INNER JOIN
						  dbo.Documente'+@strTabelaAn+'  b ON a.IDDocument = b.IDDocument'		
    IF @existTable = 0
			EXEC(@Comanda)
		
END		

GO
	
GO





IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Arhive' AND COLUMN_NAME = 'BalCon')
BEGIN
	ALTER TABLE dbo.Arhive ADD BalCon bit NULL CONSTRAINT DF_Arhive_Balcon DEFAULT 0
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Arhive' AND COLUMN_NAME = 'Fares')
BEGIN
	ALTER TABLE dbo.Arhive ADD Fares bit NULL CONSTRAINT DF_Arhive_Fares DEFAULT 0
END
GO



IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[InchideAnFares]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InchideAnFares]
GO


CREATE PROCEDURE [dbo].[InchideAnFares]  
@pAn as int,
@codUnit as smallint AS
BEGIN

---creaza tabelele corespunzatoare
EXEC CreazaArhivaFares @An = @pAn
DECLARE @strAn varchar(4)
DECLARE @Comanda varchar(5000)
DECLARE @strCodUnitate varchar(40)

SET @strAn=CAST(@pAn AS varchar(4))
SET @strCodUnitate =  CAST(@codUnit AS varchar(20))

BEGIN TRAN 
PRINT('Documente')
--ArticoleAliniate---------------------------------------
SET @Comanda = 'DELETE FROM [DocumentePlataDetaliu'+@strAn+'] WHERE IDDocumentPlata IN (SELECT IDDocumentPlata FROM [DocumentePlata'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate + ')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [DocumenteDetaliu'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [Documente'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [DocumentePlata'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE



SET @Comanda = 'INSERT INTO [Documente'+@strAn+'] SELECT * FROM [Documente] a WHERE a.[CodUnitate]=' + @strCodUnitate + ' AND  YEAR(a.DataDoc)=' +@strAn
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('DocumenteDetaliu')
--Bilant---------------------------------------


SET @Comanda = 'INSERT INTO [DocumenteDetaliu'+@strAn+']  SELECT * FROM [DocumenteDetaliu] WHERE IDDocument IN (SELECT IDDocument FROM [Documente'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate+')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE



PRINT('DocumentePlata')
--ArticoleAliniate---------------------------------------

SET @Comanda = 'INSERT INTO [DocumentePlata'+@strAn+'] SELECT * FROM [DocumentePlata] a WHERE a.[CodUnitate]=' + @strCodUnitate + ' AND  YEAR(a.Data)=' +@strAn
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('DocumentePlataDetaliu')
--Bilant---------------------------------------

SET @Comanda = 'INSERT INTO [DocumentePlataDetaliu'+@strAn+']  SELECT * FROM [DocumentePlataDetaliu] WHERE IDDocument IN (SELECT IDDocument FROM [Documente'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate+')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE




PRINT('Sterge datele arhivate')

SET @Comanda = 'DELETE FROM [DocumentePlataDetaliu] WHERE IDDocument IN (SELECT IDDocument FROM [DocumentePlata'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate+')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [DocumentePlata] WHERE [CodUnitate]=' + @strCodUnitate + ' AND  YEAR(Data)=' +@strAn
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [DocumenteDetaliu] WHERE IDDocument IN (SELECT IDDocument FROM [Documente'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate+')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [Documente] WHERE [CodUnitate]=' + @strCodUnitate + ' AND  YEAR(DataDoc)=' +@strAn
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE




IF EXISTS(SELECT * FROM [Arhive] WHERE [Anul]=@pAn AND [CodUnitate]=@codUnit)
BEGIN
	SET @Comanda = 'UPDATE [Arhive] SET [Fares]=1 WHERE [CodUnitate]=' + @strCodUnitate + 'AND [Anul]=' + @strAn;
END
ELSE
BEGIN
SET @Comanda = 'INSERT INTO [Arhive] ([Denumire],[CodUnitate],[Anul],[Fares])  VALUES
           (''' + @strAn + ''',' + @strCodUnitate + ',' + @strAn + ',1)'
END
	
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE	

SET @Comanda = 'UPDATE [FaresSetup] SET [AnCurent] = [AnCurent] + 1 WHERE [CodUnitate] =' + @strCodUnitate 
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE	


COMMIT

GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare la inchiderea anului!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
Gata:

END

GO


IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[InchideAn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InchideAn]
GO



CREATE PROCEDURE [dbo].[InchideAn]  
@pAn as int,
@codUnit as smallint AS
BEGIN

---creaza tabelele corespunzatoare
EXEC CreazaArhiva @An = @pAn
DECLARE @strAn varchar(4)
DECLARE @Comanda varchar(5000)
DECLARE @strCodUnitate varchar(40)

SET @strAn=CAST(@pAn AS varchar(4))
SET @strCodUnitate =  CAST(@codUnit AS varchar(20))

BEGIN TRAN 
PRINT('ArticoleAliniate')
--ArticoleAliniate---------------------------------------
SET @Comanda = 'DELETE FROM [ArticoleAliniate'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'INSERT INTO [ArticoleAliniate'+@strAn+'] SELECT * FROM [ArticoleAliniate] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('Bilant')
--Bilant---------------------------------------
SET @Comanda = 'DELETE FROM [Bilant'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'INSERT INTO [Bilant'+@strAn+'] SELECT * FROM [Bilant] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('Sterge rulaje')
SET @Comanda = 'DELETE FROM [Rulaje'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE
PRINT('Sterge rulajeDoc')
SET @Comanda = 'DELETE FROM [RulajeDoc'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('Conturi')
--Conturi---------------------------------------
SET @Comanda = 'DELETE FROM [Conturi'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'INSERT INTO [Conturi'+@strAn+'] SELECT * FROM [Conturi] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('Conturi Valuta')
--ConturiValuta---------------------------------------
SET @Comanda = 'DELETE FROM [ConturiValuta'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'INSERT INTO [ConturiValuta'+@strAn+'] SELECT * FROM [ConturiValuta] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('calculeaza rulajul total in credit precedent int abela conturi')
--calculeaza rulajul total in credit precedent int abela conturi

UPDATE [Conturi] SET [DebitPrecedent] = 0, [CreditPrecedent]=0, [RulajDebit]=0, [RulajCredit]=0 WHERE [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE

UPDATE [Conturi] SET 
RulajDebit = DebitInitial + (SELECT coalesce(SUM(r.ValoareLei),0) FROM Rulaje r JOIN RulajeDoc rd ON r.IDDocument=rd.IDDocument WHERE
 year(rd.DataDoc)=@pAn and dbo.IsContInclus(Cont,r.ContDebit,Terminal)=1 AND r.[CodUnitate]=@CodUnit)
WHERE cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE

UPDATE [Conturi] SET 
RulajCredit = CreditInitial+(SELECT Coalesce(SUM(r.ValoareLei),0) FROM Rulaje r JOIN RulajeDoc rd ON r.IDDocument=rd.IDDocument WHERE 
year(rd.DataDoc)=@pAn and  dbo.IsContInclus(Cont ,r.ContCredit,Terminal)=1 AND r.[CodUnitate]=@codUnit)
WHERE cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE

PRINT('calculeaza soldurile finale pe terminale')
--calculeaza soldurile finale pe terminale
UPDATE [Conturi] SET [DebitPrecedent] = [RulajDebit] - [RulajCredit], [CreditPrecedent]=0 WHERE (Tip='A' OR (Tip='B' and ([RulajDebit] - [RulajCredit]>0)))  AND Cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE
UPDATE [Conturi] SET [CreditPrecedent] = [RulajCredit] - [RulajDebit],[DebitPrecedent]=0 WHERE (Tip='P' OR (Tip='B' and ([RulajDebit] - [RulajCredit]<0)))  AND Cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE
--modifica soldurile initiale
UPDATE [Conturi] SET [DebitInitial] = [DebitPrecedent],[CreditInitial] = [CreditPrecedent] WHERE Cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE
UPDATE [Conturi] SET [DebitInitial] = 0,[CreditInitial] = 0 WHERE Categorie<>0 AND [CodUnitate]=@CodUnit
IF @@ERROR != 0 GOTO EROARE

UPDATE [Conturi] SET [DebitPrecedent] = 0, [CreditPrecedent]=0, [RulajDebit]=0, [RulajCredit]=0 WHERE [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE

PRINT('JurnaleSolduriInitiale')
--JurnaleSolduriInitiale---------------------------------------
SET @Comanda = 'DELETE FROM [JurnaleSolduriInitiale'+@strAn+'] WHERE [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'INSERT INTO [JurnaleSolduriInitiale'+@strAn+'] SELECT * FROM [JurnaleSolduriInitiale]  WHERE [CodUnitate]='+@strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('Ordonantari')
--Ordonantari---------------------------------------
SET @Comanda = 'DELETE FROM [Ordonantari'+@strAn+']  WHERE [CodUnitate]='+@strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'INSERT INTO [Ordonantari'+@strAn+'] SELECT * FROM [Ordonantari] WHERE YEAR(DataDoc)='+@strAn +' AND [CodUnitate]='+@strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DELETE FROM [Ordonantari] WHERE YEAR(DataDoc)='+@strAn +' AND [CodUnitate]=' + @strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('RezultatPatrimonial')
--RezultatPatrimonial---------------------------------------
SET @Comanda = 'DELETE FROM [RezultatPatrimonial'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate
PRINT(@Comanda)
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'INSERT INTO [RezultatPatrimonial'+@strAn+'] SELECT * FROM [RezultatPatrimonial] WHERE [CodUnitate]='+@strCodUnitate
PRINT(@Comanda)
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('RulajeDoc')
--RulajeDoc---------------------------------------

SET @Comanda = 'INSERT INTO [RulajeDoc'+@strAn+'] SELECT * FROM [RulajeDoc] WHERE YEAR(DataDoc)='+@strAn +' AND [CodUnitate]='+@strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('Rulaje')
--Rulaje---------------------------------------

SET @Comanda = 'INSERT INTO [Rulaje'+@strAn+'] SELECT * FROM [Rulaje] WHERE IDDocument in (SELECT IDDocument FROM [RulajeDoc'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate+')'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE


SET @Comanda = 'DELETE FROM [RulajeDoc] WHERE YEAR(DataDoc)='+@strAn + ' AND [CodUnitate]='+@strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE



PRINT('VenituriSiCheltuieli')
--VenituriSiCheltuieli---------------------------------------
SET @Comanda = 'DELETE FROM [VenituriSiCheltuieli'+@strAn+'] WHERE [CodUnitate]='+@strCodUnitate
EXEC(@Comanda) 

SET @Comanda = 'INSERT INTO [VenituriSiCheltuieli'+@strAn+'] SELECT * FROM [VenituriSiCheltuieli] WHERE [CodUnitate]='+@strCodUnitate
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE	

IF EXISTS(SELECT * FROM [Arhive] WHERE [Anul]=@pAn AND [CodUnitate]=@codUnit)
BEGIN
	SET @Comanda = 'UPDATE [Arhive] SET [Balcon]=1 WHERE [CodUnitate]=' + @strCodUnitate + 'AND [Anul]=' + @strAn;
END
ELSE
BEGIN
SET @Comanda = 'INSERT INTO [Arhive] ([Denumire],[CodUnitate],[Anul],[Balcon])  VALUES
           (''' + @strAn + ''',' + @strCodUnitate + ',' + @strAn + ',1)'
END
	
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE	

SET @Comanda = 'UPDATE [BalConSetup] SET [AnCurent] = [AnCurent] + 1 WHERE [CodUnitate] =' + @strCodUnitate 
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE	



COMMIT

GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare la inchiderea anului!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
Gata:

END
GO


IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[CreazaArhiva]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreazaArhiva]
GO

CREATE PROCEDURE [dbo].[CreazaArhiva]
	@An smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @strContAn varchar(40)
	DECLARE @Comanda varchar(3000)
	DECLARE @existTableCont bit
	
	SET @strContAn = 'Conturi' + cast(@An AS varchar(4)) 
	SET @existTableCont = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strContAn)
		SET @existTableCont = 1
	
	--verifica existenta tabeleli arhiva
	SET @Comanda = 'CREATE TABLE [' + @strContAn + '] (
		[Cont] [dbo].[TipCont] NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
		[Denumire] [varchar](200) NOT NULL,
		[Tip] [char](1) NOT NULL,
		[Terminal] [bit] NOT NULL,
		[ContParinte] [dbo].[TipCont] NOT NULL,
		[EsteAnalitic] [bit] NOT NULL,
		[DebitInitial] [decimal](20, 2) NOT NULL,
		[CreditInitial] [decimal](20, 2) NOT NULL,
		[DebitPrecedent] [decimal](20, 2) NOT NULL,
		[CreditPrecedent] [decimal](20, 2) NOT NULL,
		[Categorie] [tinyint] NOT NULL,
		[RulajDebit] [decimal](20, 2) NOT NULL,
		[RulajCredit] [decimal](20, 2) NOT NULL,
		[Sold] [decimal](20, 2) NOT NULL,
		[SortValue] [dbo].[TipCont] NOT NULL,
	CONSTRAINT [PK_'+@strContAn +'] PRIMARY KEY CLUSTERED(
		[Cont] ASC,
		[CodUnitate] ASC
	)) ON [PRIMARY]'
	
	IF @existTableCont = 0
		EXEC(@Comanda)
		
	SET @Comanda = 'ALTER TABLE [' + @strContAn +']  WITH CHECK ADD  CONSTRAINT [FK_'+@strContAn +'_' + @strContAn + '] FOREIGN KEY([ContParinte], [CodUnitate])
		REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate])'
	IF @existTableCont = 0
		EXEC(@Comanda)
		
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] CHECK CONSTRAINT [FK_'+@strContAn +'_' + @strContAn + ']'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strContAn+'_Unitati] FOREIGN KEY([CodUnitate])
		REFERENCES [dbo].[Unitati] ([CodUnitate])'
	IF @existTableCont = 0
		EXEC(@Comanda)	
		
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] CHECK CONSTRAINT [FK_'+@strContAn+'_Unitati]'
	IF @existTableCont = 0
		EXEC(@Comanda)	
		
		
	SET @Comanda = 'ALTER TABLE ['+@strContAn+']  WITH NOCHECK ADD  CONSTRAINT [CK_'+@strContAn+'_Tip] CHECK  (([Tip]=''A'' OR [Tip]=''P'' OR [Tip]=''B''))'
	IF @existTableCont = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] CHECK CONSTRAINT [CK_'+@strContAn+'_Tip]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_Simbol]  DEFAULT ('''') FOR [Cont]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_Denumire]  DEFAULT ('''') FOR [Denumire]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_Tip]  DEFAULT (''A'') FOR [Tip]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_Terminal]  DEFAULT ((1)) FOR [Terminal]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_CodContParinte]  DEFAULT ('''') FOR [ContParinte]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_EsteAnalitic]  DEFAULT ((1)) FOR [EsteAnalitic]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_DebitInitial]  DEFAULT ((0)) FOR [DebitInitial]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_CreditInitial]  DEFAULT ((0)) FOR [CreditInitial]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_DebitPrecedent]  DEFAULT ((0)) FOR [DebitPrecedent]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_CreditPrecedent]  DEFAULT ((0)) FOR [CreditPrecedent]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_Categorie]  DEFAULT ((0)) FOR [Categorie]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_RulajDebit]  DEFAULT ((0)) FOR [RulajDebit]'
	IF @existTableCont = 0
		EXEC(@Comanda)
	
	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_RulajCredit]  DEFAULT ((0)) FOR [RulajCredit]'
	IF @existTableCont = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_Sold]  DEFAULT ((0)) FOR [Sold]'
	IF @existTableCont = 0
		EXEC(@Comanda)	

	SET @Comanda = 'ALTER TABLE ['+@strContAn+'] ADD  CONSTRAINT [DF_'+@strContAn+'_SortValue]  DEFAULT ('''') FOR [SortValue]'
	IF @existTableCont = 0
		EXEC(@Comanda)

	DECLARE @strAAAn varchar(40)
	DECLARE @existTableAA bit
	
	SET @strAAAn = 'ArticoleAliniate' + cast(@An AS varchar(4)) 
	SET @existTableAA = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strAAAn)
		SET @existTableAA = 1

	SET @Comanda = 'CREATE TABLE ['+@strAAAn+'](
	[ArtAl] [varchar](40) NOT NULL,
	[Denumire] [varchar](200) NOT NULL,
	[AAParinte] [varchar](40) NOT NULL,
	[Terminal] [bit] NOT NULL,
	[Titlu] [varchar](100) NOT NULL,
	[Categorii] [varchar](50) NOT NULL,
	[TermDenumire] [varchar](100) NOT NULL,
	[StartDenumire] [varchar](100) NOT NULL,
	[Anexa6] [bit] NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	CONSTRAINT [PK_'+@strAAAn+'] PRIMARY KEY CLUSTERED(
		[ArtAl] ASC,
		[CodUnitate] ASC
	)) ON [PRIMARY]'
	IF @existTableAA = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strAAAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strAAAn+'_'+@strAAAn+'] FOREIGN KEY([AAParinte], [CodUnitate])
		REFERENCES ['+@strAAAn+'] ([ArtAl], [CodUnitate])'
	IF @existTableAA = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE [dbo].['+@strAAAn+'] CHECK CONSTRAINT [FK_'+@strAAAn+'_'+@strAAAn+']'
	IF @existTableAA = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE [dbo].['+@strAAAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strAAAn+'_Unitati] FOREIGN KEY([CodUnitate])
		REFERENCES [dbo].[Unitati] ([CodUnitate])'
	IF @existTableAA = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE [dbo].['+@strAAAn+'] CHECK CONSTRAINT [FK_'+@strAAAn+'_Unitati]'
	IF @existTableAA = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE [dbo].['+@strAAAn+'] ADD  CONSTRAINT [DF_'+@strAAAn+'_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]'
	IF @existTableAA = 0
		EXEC(@Comanda)

	---Bilant----------------------------
	DECLARE @strBilantAn varchar(40)
	DECLARE @existTableBilant bit
	
	SET @strBilantAn = 'Bilant' + cast(@An AS varchar(4)) 
	SET @existTableBilant = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strBilantAn)
		SET @existTableBilant = 1

	SET @Comanda = 'CREATE TABLE ['+@strBilantAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](10) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	[Boldat] [bit] NOT NULL,
	CONSTRAINT [PK_'+@strBilantAn+'] PRIMARY KEY CLUSTERED( 
		[row] ASC)
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
	IF @existTableBilant = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strBilantAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strBilantAn+'_Unitati] FOREIGN KEY([CodUnitate])
		REFERENCES [dbo].[Unitati] ([CodUnitate])'
	IF @existTableBilant = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strBilantAn+'] CHECK CONSTRAINT [FK_'+@strBilantAn+'_Unitati]'
	IF @existTableBilant = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strBilantAn+'] ADD  CONSTRAINT [DF_'+@strBilantAn+'_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]'
	IF @existTableBilant = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strBilantAn+'] ADD  CONSTRAINT [DF_'+@strBilantAn+'_Boldat]  DEFAULT ((0)) FOR [Boldat]'
	IF @existTableBilant = 0
		EXEC(@Comanda)

	---Conturi Valuta--------------------
	
	DECLARE @strContValutaAn varchar(40)
	DECLARE @existTableContValuta bit
	
	SET @strContValutaAn = 'ConturiValuta' + cast(@An AS varchar(4)) 
	SET @existTableContValuta = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strContValutaAn)
		SET @existTableContValuta = 1

	SET @Comanda = 'CREATE TABLE ['+@strContValutaAn+'](
	[CodValuta] [smallint] NOT NULL,
	[Cont] [dbo].[TipCont] NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	[DebitInitial] [decimal](20, 2) NOT NULL,
	[CreditInitial] [decimal](20, 2) NOT NULL,
	[DebitPrecedent] [decimal](20, 2) NOT NULL,
	[CreditPrecedent] [decimal](20, 2) NOT NULL,
	[IdJurnal] [int] NOT NULL,
	CONSTRAINT [PK_'+@strContValutaAn+'] PRIMARY KEY CLUSTERED(
	[CodValuta] ASC,
	[Cont] ASC,
	[CodUnitate] ASC,
	[IdJurnal] ASC)) ON [PRIMARY]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strContValutaAn+'_'+@strContAn+'] FOREIGN KEY([Cont], [CodUnitate])
		REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate])'
	IF @existTableContValuta = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] CHECK CONSTRAINT [FK_'+@strContValutaAn+'_'+@strContAn+']'
	IF @existTableContValuta = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strContValutaAn+'_Jurnale] FOREIGN KEY([IdJurnal])
		REFERENCES [dbo].[Jurnale] ([IDJurnal])'
	IF @existTableContValuta = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] CHECK CONSTRAINT [FK_'+@strContValutaAn+'_Jurnale]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strContValutaAn+'_Unitati] FOREIGN KEY([CodUnitate])
		REFERENCES [dbo].[Unitati] ([CodUnitate])'
	IF @existTableContValuta = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] CHECK CONSTRAINT [FK_'+@strContValutaAn+'_Unitati]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+']  WITH NOCHECK ADD  CONSTRAINT [FK_'+@strContValutaAn+'_Valuta] FOREIGN KEY([CodValuta])
		REFERENCES [dbo].[Valuta] ([CodValuta])'
	IF @existTableContValuta = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] CHECK CONSTRAINT [FK_'+@strContValutaAn+'_Valuta]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] ADD  CONSTRAINT [DF_'+@strContValutaAn+'_CodValuta]  DEFAULT ((0)) FOR [CodValuta]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] ADD  CONSTRAINT [DF_'+@strContValutaAn+'_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] ADD  CONSTRAINT [DF_'+@strContValutaAn+'_DebitInitial]  DEFAULT ((0)) FOR [DebitInitial]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] ADD  CONSTRAINT [DF_'+@strContValutaAn+'_CreditInitial]  DEFAULT ((0)) FOR [CreditInitial]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] ADD  CONSTRAINT [DF_'+@strContValutaAn+'_DebitPrecedent]  DEFAULT ((0)) FOR [DebitPrecedent]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] ADD  CONSTRAINT [DF_'+@strContValutaAn+'_CreditPrecedent]  DEFAULT ((0)) FOR [CreditPrecedent]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strContValutaAn+'] ADD  CONSTRAINT [DF_'+@strContValutaAn+'_IdJurnal]  DEFAULT ((0)) FOR [IdJurnal]'
	IF @existTableContValuta = 0
		EXEC(@Comanda)

	---Jurnale Solduri Initiale--------------------
	
	DECLARE @strJurnaleSIAn varchar(40)
	DECLARE @existTableJurnaleSI bit
	
	SET @strJurnaleSIAn = 'JurnaleSolduriInitiale' + cast(@An AS varchar(4)) 
	SET @existTableJurnaleSI = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strJurnaleSIAn)
		SET @existTableJurnaleSI = 1

	SET @Comanda = 'CREATE TABLE ['+@strJurnaleSIAn+'](
	[IDJurnal] [int] NOT NULL,
	[Cont] [dbo].[TipCont] NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	[DebitInitial] [decimal](18, 2) NOT NULL,
	[CreditInital] [decimal](18, 2) NOT NULL,
	[DebitPrecedent] [decimal](20, 2) NOT NULL,
	[CreditPrecedent] [decimal](20, 2) NOT NULL,
	CONSTRAINT [PK_'+@strJurnaleSIAn+'] PRIMARY KEY CLUSTERED 
	(
	[IDJurnal] ASC,
	[Cont] ASC,
	[CodUnitate] ASC
	)) ON [PRIMARY]'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strJurnaleSIAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strJurnaleSIAn+'_' + @strContAn + '] FOREIGN KEY([Cont], [CodUnitate])
		REFERENCES [' + @strContAn + '] ([Cont], [CodUnitate])'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strJurnaleSIAn+'] CHECK CONSTRAINT [FK_'+@strJurnaleSIAn+'_' + @strContAn + ']'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strJurnaleSIAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strJurnaleSIAn+'_Jurnale] FOREIGN KEY([IDJurnal])
		REFERENCES [Jurnale] ([IDJurnal])'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strJurnaleSIAn+'] CHECK CONSTRAINT [FK_'+@strJurnaleSIAn+'_Jurnale]'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strJurnaleSIAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strJurnaleSIAn+'_Unitati] FOREIGN KEY([CodUnitate])
		REFERENCES [Unitati] ([CodUnitate])'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strJurnaleSIAn+'] CHECK CONSTRAINT [FK_'+@strJurnaleSIAn+'_Unitati]'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strJurnaleSIAn+'] ADD  CONSTRAINT [DF_'+@strJurnaleSIAn+'_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strJurnaleSIAn+'] ADD  CONSTRAINT [DF_'+@strJurnaleSIAn+'_DebitInitial]  DEFAULT ((0)) FOR [DebitInitial]'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strJurnaleSIAn+'] ADD  CONSTRAINT [DF_'+@strJurnaleSIAn+'_DebitPrecedent]  DEFAULT ((0)) FOR [DebitPrecedent]'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strJurnaleSIAn+'] ADD  CONSTRAINT [DF_'+@strJurnaleSIAn+'_CreditPrecedent]  DEFAULT ((0)) FOR [CreditPrecedent]'
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)

	---Ordonantari--------------------
	
	DECLARE @strOrdonantariAn varchar(40)
	DECLARE @existTableOrdonantari bit
	
	SET @strOrdonantariAn = 'Ordonantari' + cast(@An AS varchar(4)) 
	SET @existTableOrdonantari = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strOrdonantariAn)
		SET @existTableOrdonantari = 1

	SET @Comanda = 'CREATE TABLE ['+@strOrdonantariAn+'](
	[NrDoc] [decimal](18, 0) NOT NULL,
	[DataDoc] [datetime] NOT NULL,
	[IDFurnizor] [bigint] NOT NULL,
	[ValoareLei] [decimal](20, 2) NOT NULL,
	[IDJurnal] [int] NOT NULL,
	[Scop] [varchar](100) NOT NULL,
	[DocJust] [varchar](50) NOT NULL,
	[AngLegal] [varchar](50) NOT NULL,
	[Achitat] [bit] NOT NULL,
	[IdOrdonantare] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT 0,
	[IdModPlata] [int] NOT NULL DEFAULT 1,
	[TotalDatorat] [decimal](18, 2) NOT NULL DEFAULT 0,
	[TotalAvansuri] [decimal](18, 2) NOT NULL DEFAULT 0,
	[ArtAl] [varchar](40) NOT NULL DEFAULT '''',
	[VizatCFP] [bit] NOT NULL,
	[MotivRefuz] [varchar](250) NOT NULL DEFAULT '''',
	[IDResponsabil] [int] NOT NULL DEFAULT 0,
	[DataVizaCFP] [datetime] NOT NULL DEFAULT (getdate()),
	[NrRegCFP] [varchar](20) NOT NULL DEFAULT (''FARA''),
	[IBANSursa] [varchar](24) NULL,
	[IBANDest] [varchar](24) NULL,
	CONSTRAINT [PK_'+@strOrdonantariAn+'] PRIMARY KEY CLUSTERED 
	(
		[IdOrdonantare] ASC
	),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_'+@strAAAn+'] FOREIGN KEY([ArtAl], [CodUnitate]) REFERENCES ['+@strAAAn+'] ([ArtAl], [CodUnitate]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Parteneri] FOREIGN KEY([IDfurnizor]) REFERENCES [dbo].[Parteneri] ([IdPartener]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_ModPlata] FOREIGN KEY([IdModPlata]) REFERENCES [dbo].[ModPlata] ([IDModPlata]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])	
	) ON [PRIMARY]'
	IF @existTableOrdonantari = 0
		EXEC(@Comanda)

	
	---RezultatPatrimonial--------------------
	
	DECLARE @strRezultatPatrimonialAn varchar(40)
	DECLARE @existTableRezultatPatrimonial bit
	
	SET @strRezultatPatrimonialAn = 'RezultatPatrimonial' + cast(@An AS varchar(4)) 
	SET @existTableRezultatPatrimonial = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRezultatPatrimonialAn)
		SET @existTableRezultatPatrimonial = 1

	SET @Comanda = 'CREATE TABLE ['+@strRezultatPatrimonialAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](10) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	[Boldat] [bit] NOT NULL,
	CONSTRAINT [PK_'+@strRezultatPatrimonialAn+'] PRIMARY KEY CLUSTERED 
	(
	[row] ASC
	)) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
	IF @existTableRezultatPatrimonial = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRezultatPatrimonialAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strRezultatPatrimonialAn+'_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])'
	IF @existTableRezultatPatrimonial = 0
		EXEC(@Comanda)
	

	SET @Comanda = 'ALTER TABLE ['+@strRezultatPatrimonialAn+'] CHECK CONSTRAINT [FK_'+@strRezultatPatrimonialAn+'_Unitati]'
	IF @existTableRezultatPatrimonial = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRezultatPatrimonialAn+'] ADD  CONSTRAINT [DF_'+@strRezultatPatrimonialAn+'_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]'
	IF @existTableRezultatPatrimonial = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRezultatPatrimonialAn+'] ADD  CONSTRAINT [DF_'+@strRezultatPatrimonialAn+'_Boldat]  DEFAULT ((0)) FOR [Boldat]'
	IF @existTableRezultatPatrimonial = 0
		EXEC(@Comanda)

	---RulajeDoc--------------------
	
	DECLARE @strRulajeDocAn varchar(40)
	DECLARE @existTableRulajeDoc bit
	
	SET @strRulajeDocAn = 'RulajeDoc' + cast(@An AS varchar(4)) 
	SET @existTableRulajeDoc = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRulajeDocAn)
		SET @existTableRulajeDoc = 1

	SET @Comanda = 'CREATE TABLE ['+@strRulajeDocAn+'](
	[IDDocument] [bigint] NOT NULL,
	[ValoareLei] [decimal](20, 2) NOT NULL,
	[ValoareValuta] [decimal](20, 2) NOT NULL,
	[NumarDoc] [decimal](18, 0) NOT NULL,
	[DataDoc] [datetime] NOT NULL,
	[NumarNota] [varchar](50) NOT NULL,
	[TipFormula] [smallint] NOT NULL,
	[IDJurnal] [int] NULL,
	[Observatii] [varchar](200) NULL,
	[xUser] [varchar](50) NOT NULL,
	[xHost] [varchar](50) NOT NULL,
	[xDate] [datetime] NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	CONSTRAINT [PK_'+@strRulajeDocAn+'] PRIMARY KEY CLUSTERED 
	(
	[IDDocument] ASC
	)) ON [PRIMARY]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+']  WITH NOCHECK ADD  CONSTRAINT [FK_'+@strRulajeDocAn+'_Jurnale] FOREIGN KEY([IDJurnal])
	REFERENCES [Jurnale] ([IDJurnal])'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] CHECK CONSTRAINT [FK_'+@strRulajeDocAn+'_Jurnale]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strRulajeDocAn+'_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [Unitati] ([CodUnitate])'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] CHECK CONSTRAINT [FK_'+@strRulajeDocAn+'_Unitati]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+']  WITH NOCHECK ADD  CONSTRAINT [CK_'+@strRulajeDocAn+'_Tip_FORMULA] CHECK  (([TipFormula] = 3 or ([TipFormula] = 2 or ([TipFormula] = 1 or [TipFormula] = 0))))'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] CHECK CONSTRAINT [CK_'+@strRulajeDocAn+'_Tip_FORMULA]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_ValoareLei]  DEFAULT (0) FOR [ValoareLei]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_ValoareValuta]  DEFAULT (0) FOR [ValoareValuta]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_NumarDoc]  DEFAULT (0) FOR [NumarDoc]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_DataDoc]  DEFAULT (getdate()) FOR [DataDoc]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_NumarNota]  DEFAULT ('''') FOR [NumarNota]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_TipFormula]  DEFAULT (0) FOR [TipFormula]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_IDJurnal]  DEFAULT (0) FOR [IDJurnal]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_xUser]  DEFAULT (suser_sname()) FOR [xUser]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_xHost]  DEFAULT (host_name()) FOR [xHost]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_xDate]  DEFAULT (0) FOR [xDate]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeDocAn+'] ADD  CONSTRAINT [DF_'+@strRulajeDocAn+'_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)

	---Rulaje--------------------
	
	DECLARE @strRulajeAn varchar(40)
	DECLARE @existTableRulaje bit
	
	SET @strRulajeAn = 'Rulaje' + cast(@An AS varchar(4)) 
	SET @existTableRulaje = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRulajeAn)
		SET @existTableRulaje = 1

	SET @Comanda = 'CREATE TABLE ['+@strRulajeAn+'](
	[CodFormula] [bigint] NOT NULL,
	[ContDebit] [dbo].[TipCont] NOT NULL,
	[ContCredit] [dbo].[TipCont] NOT NULL,
	[CodValuta] [smallint] NOT NULL,
	[IDDocument] [bigint] NOT NULL,
	[ValoareValuta] [decimal](20, 2) NOT NULL,
	[Curs] [decimal](18, 4) NOT NULL,
	[ValoareLei] [decimal](20, 2) NOT NULL,
	[DataDocJust] [datetime] NOT NULL,
	[TipDocJust] [varchar](10) NOT NULL,
	[NumarDocJust] [decimal](18, 0) NOT NULL,
	[ExplicatiiDocJust] [varchar](200) NOT NULL,
	[NrCrt] [smallint] NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	CONSTRAINT [PK_'+@strRulajeAn+'] PRIMARY KEY CLUSTERED 
	(
	[CodFormula] ASC
	)) ON [PRIMARY]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strRulajeAn+'_'+@strContAn+'_Credit] FOREIGN KEY([ContCredit], [CodUnitate])
	REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate])'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] CHECK CONSTRAINT [FK_'+@strRulajeAn+'_'+@strContAn+'_Credit]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)
		
	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strRulajeAn+'_'+@strContAn+'_Debit] FOREIGN KEY([ContDebit], [CodUnitate])
		REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate])'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] CHECK CONSTRAINT [FK_'+@strRulajeAn+'_'+@strContAn+'_Debit]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+']  WITH NOCHECK ADD  CONSTRAINT [FK_'+@strRulajeAn+'_'+@strRulajeDocAn+'] FOREIGN KEY([IDDocument])
		REFERENCES ['+@strRulajeDocAn+'] ([IDDocument])
		ON UPDATE CASCADE
		ON DELETE CASCADE'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] CHECK CONSTRAINT [FK_'+@strRulajeAn+'_'+@strRulajeDocAn+']'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+']  WITH CHECK ADD  CONSTRAINT [FK_'+@strRulajeAn+'_Unitati] FOREIGN KEY([CodUnitate])
		REFERENCES [dbo].[Unitati] ([CodUnitate])'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] CHECK CONSTRAINT [FK_'+@strRulajeAn+'_Unitati]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+']  WITH NOCHECK ADD  CONSTRAINT [FK_'+@strRulajeAn+'_Valuta] FOREIGN KEY([CodValuta])
	REFERENCES [dbo].[Valuta] ([CodValuta])'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] CHECK CONSTRAINT [FK_'+@strRulajeAn+'_Valuta]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] ADD  CONSTRAINT [DF_'+@strRulajeAn+'_CodValuta]  DEFAULT (0) FOR [CodValuta]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] ADD  CONSTRAINT [DF_'+@strRulajeAn+'_ValareaValuta]  DEFAULT (0) FOR [ValoareValuta]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] ADD  CONSTRAINT [DF_'+@strRulajeAn+'_Curs]  DEFAULT (1) FOR [Curs]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] ADD  CONSTRAINT [DF_'+@strRulajeAn+'_ValoareLei]  DEFAULT (0) FOR [ValoareLei]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] ADD  CONSTRAINT [DF_'+@strRulajeAn+'_TipDocJust]  DEFAULT (''NC'') FOR [TipDocJust]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] ADD  CONSTRAINT [DF_'+@strRulajeAn+'_NrCrt]  DEFAULT (1) FOR [NrCrt]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)

	SET @Comanda = 'ALTER TABLE ['+@strRulajeAn+'] ADD  CONSTRAINT [DF_'+@strRulajeAn+'_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]'
	IF @existTableRulaje = 0
		EXEC(@Comanda)
		
	---VenituriSiCheltuieli--------------------
	
	DECLARE @strVenituriSiCheltuieliAn varchar(40)
	DECLARE @existTableVenituriSiCheltuieli bit
	
	SET @strVenituriSiCheltuieliAn = 'VenituriSiCheltuieli' + cast(@An AS varchar(4)) 
	SET @existTableVenituriSiCheltuieli = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strVenituriSiCheltuieliAn)
		SET @existTableVenituriSiCheltuieli = 1

	SET @Comanda = 'CREATE TABLE ['+@strVenituriSiCheltuieliAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](20) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	[Boldat] [bit] NOT NULL,
	CONSTRAINT [PK_'+@strVenituriSiCheltuieliAn+'] PRIMARY KEY CLUSTERED 
	(
	[row] ASC
	)) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
	IF @existTableVenituriSiCheltuieli = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strVenituriSiCheltuieliAn+'] ADD  CONSTRAINT [DF_'+@strVenituriSiCheltuieliAn+'_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]'
	IF @existTableVenituriSiCheltuieli = 0
		EXEC(@Comanda)


	SET @Comanda = 'ALTER TABLE ['+@strVenituriSiCheltuieliAn+'] ADD  CONSTRAINT [DF_'+@strVenituriSiCheltuieliAn+'_Boldat]  DEFAULT ((0)) FOR [Boldat]'
	IF @existTableVenituriSiCheltuieli = 0
		EXEC(@Comanda)



		

END
GO

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
DELETE FROM [admin_ApplicationMenu] WHERE [ApplicationId] = '892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9'
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3100,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Documente intrate(cumparare)',NULL,100,'Documente_intrare',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3200,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Documente iesite(vanzare)',NULL,110,'Documente_iesire',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3500,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Incasari, plati',NULL,115,'Incasari_Plati',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3300,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Parteneri',NULL,120,'Parteneri',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4200,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Articole',NULL,130,'Articole',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3400,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Setari',NULL,140,NULL,NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4100,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Import date',NULL,150,'Import',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3600,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Consultare Arhiva',NULL,155,'Consultare_Arhiva',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4800,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Liste',NULL,160,NULL,NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4000,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Unitati functionale',NULL,170,'Unitati_functionale',NULL,NULL)

INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3401,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Parametrizare Modul',3400,20,'ParametrizareModul',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3402,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Banci',3400,21,'Banci',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3403,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Trasee',3400,22,'Trasee',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3404,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Locatii vizitate',3400,23,'Locatii',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3405,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Regim fiscal',3400,24,'Regimuri_Fiscale',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3406,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Coduri CPV',3400,25,'CoduriCPV',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3407,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Categorii Articole',3400,26,'CategoriiArticole',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3408,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Tipuri Dimensiuni',3400,27,'TipuriDimensiuni',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3409,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Note Contabile',3400,28,'NoteContabile',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3410,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Semnaturi',3400,29,'Semnaturi',NULL,NULL)

INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4801,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Categorii Articole',4800,40,'RaportCategoriiArticole',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4802,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Jurnale',4800,41,'RaportJurnale',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4803,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Liste Documente',4800,42,'RaportDocumente',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4804,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Operatiuni Contabile',4800,43,'RaportContabil',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4805,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Facturi',4800,44,'RaportFacturi',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4806,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Operatiuni incasare/plata',4800,45,'RaportOperatiuniIncasarePlata',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4807,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Fisa Partenerului',4800,46,'RaportFisaPartenerului',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4808,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Export Declaratii',4800,47,'ExportDeclaratii',NULL,NULL)
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
GO




IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[fares_GenereazaDocumentePentruPlata]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaDocumentePentruPlata]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[fares_GenereazaDocumentePentruPlata]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@IDPartener bigint,
	@TipOperatiune varchar(10),
	@An varchar(4)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @cmd varchar(8000)

    SET @cmd = 'SELECT doc.IDDocument,NrDoc,DataDoc,NrDoc + ''/'' +CONVERT(varchar(20),DataDoc,104) NrDataDoc ,IDPartener,CodUnitate,Total,
	Coalesce(Achitat,0) Achitat, Total - Coalesce(Achitat,0) RestPlata,DataScadenta
	FROM [Documente'+@An+'] doc 
	JOIN TipDocument tdoc ON doc.IDTipDocument = tdoc.IDTipDocument and tDoc.SePlateste=1
	LEFT OUTER JOIN
	(SELECT IDDocument,SUM(Valoare) Achitat FROM [DocumentePlataDetaliu'+@An+'] dpd GROUP BY IDDocument) pldoc ON pldoc.IDDocument=doc.IDDocument
	WHERE CodUnitate='+CAST(@CodUnitate AS Varchar(5))+' AND IDPartener = '+CAST(@IDPartener AS Varchar(10))+' AND
	tdoc.TipOperatie like ''%'+@TipOperatiune+'%''
	
	UNION
	SELECT NULL, '''',GETDATE(),''AVANS'','+CAST(@IDPartener AS varchar(20))+','+CAST(@CodUnitate AS varchar(3))+',0,0,0,GETDATE() 
	FROM FaresSetup WHERE CodUnitate='+CAST(@CodUnitate AS Varchar(3))+'
	ORDER BY DataDoc DESC'
	
	/*SELECT doc.IDDocument,NrDoc,DataDoc,NrDoc + '/' +CONVERT(varchar(20),DataDoc,104) NrDataDoc ,IDPartener,CodUnitate,Total,
	Coalesce(Achitat,0) Achitat, Total - Coalesce(Achitat,0) RestPlata,DataScadenta
	FROM [Documente] doc 
	JOIN TipDocument tdoc ON doc.IDTipDocument = tdoc.IDTipDocument and tDoc.SePlateste=1
	LEFT OUTER JOIN
	(SELECT IDDocument,SUM(Valoare) Achitat FROM [DocumentePlataDetaliu] dpd GROUP BY IDDocument) pldoc ON pldoc.IDDocument=doc.IDDocument
	WHERE CodUnitate=@CodUnitate AND IDPartener = @IDPartener AND
	tdoc.TipOperatie like '%'+@TipOperatiune+'%'
	
	UNION
	SELECT NULL, '',GETDATE(),'AVANS',@IDPartener,@CodUnitate,0,0,0,GETDATE() 
	FROM FaresSetup WHERE CodUnitate=@CodUnitate
	ORDER BY DataDoc DESC*/
	
	EXEC(@cmd)
END

GO


IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[fares_FiltreazaDocumente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaDocumente]
GO


CREATE PROCEDURE [dbo].[fares_FiltreazaDocumente] 
	-- Add the parameters for the stored procedure here
	@filtru varchar(500),
	@an varchar(4)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [Documente'+@an+'] where IDDocument in 
		(select [Documente'+@an+'].IDDocument FROM [Documente'+@an+']  JOIN [DocumenteDetaliu'+@an+']  ON [Documente'+@an+'].IDDocument = [DocumenteDetaliu'+@an+'].IDDocument WHERE ' +@filtru  + ')'
	EXEC(@sel)
END


GO



IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Unitati' AND COLUMN_NAME = 'Cumulativa')
BEGIN
	ALTER TABLE Unitati ADD
	Cumulativa bit NULL,
	CodUnitateParinte smallint NULL

	ALTER TABLE Unitati ADD CONSTRAINT
	FK_Unitati_UnitatiCumulative FOREIGN KEY
	(
	CodUnitateParinte
	) REFERENCES dbo.Unitati
	(
	CodUnitate
	) 
	ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 

END
GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[eBuget_ConturiBugetate]'))
DROP VIEW [dbo].[eBuget_ConturiBugetate]
GO

CREATE VIEW [dbo].[eBuget_ConturiBugetate]
AS
SELECT     dbo.ConturiSpeciale.Tipuri, dbo.Conturi.*
FROM         dbo.Conturi INNER JOIN
                      dbo.ConturiSpeciale ON dbo.Conturi.Cont = dbo.ConturiSpeciale.Cont AND dbo.Conturi.CodUnitate = dbo.ConturiSpeciale.CodUnitate

GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[PersoaneVizaCFP]'))
DROP VIEW [dbo].[PersoaneVizaCFP]
GO

CREATE VIEW [dbo].[PersoaneVizaCFP]
AS
SELECT     dbo.Persoane.NumePrenume, dbo.ResponsabilVizaCFP.CodUnitate, dbo.ResponsabilVizaCFP.DataActivare, dbo.ResponsabilVizaCFP.IDResponsabil, 
                      dbo.ResponsabilVizaCFP.IDPersoana, dbo.Persoane.IdPersoana AS Expr1
FROM         dbo.ResponsabilVizaCFP INNER JOIN
                      dbo.Persoane ON dbo.ResponsabilVizaCFP.IDPersoana = dbo.Persoane.IdPersoana

GO


/****** Object:  View [dbo].[exbug_RegistrulProiectelor]    Script Date: 04/27/2011 15:19:48 ******/
IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[exbug_RegistrulProiectelor]'))
DROP VIEW [dbo].[exbug_RegistrulProiectelor]
GO




CREATE VIEW [dbo].[exbug_RegistrulProiectelor]
AS
SELECT     'Ordonantare de plata' AS Den_Doc, o.NrDoc, o.DataDoc, o.Scop, p.NumePrenume, o.ValoareLei, o.DataVizaCFP, o.Achitat, o.MotivRefuz, o.VizatCFP, 
                      d.Nume AS departament
FROM         dbo.Ordonantari AS o INNER JOIN
                      dbo.PersoaneVizaCFP AS p ON o.IDResponsabil = o.IDResponsabil INNER JOIN
                      dbo.AngajatiSalarizare AS a ON a.IdPersoana = p.IDPersoana INNER JOIN
                      dbo.Departamente d ON a.IDDepartament = d.IDDepartament
GO



IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[balcon_GenerareAnaliticeArticoleAlineate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenerareAnaliticeArticoleAlineate]
GO



-- =============================================
-- Author:		Dorin Popa
-- Create date: 21.05.2011
-- Description:	Genereaza analitice pentru conturile care au articole si alineate
-- =============================================
CREATE PROCEDURE [dbo].[balcon_GenerareAnaliticeArticoleAlineate] 
	-- Add the parameters for the stored procedure here
	@Cont TipCont, 
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [Conturi]
           ([Cont]
           ,[CodUnitate]
           ,[Denumire]
           ,[Tip]
           ,[Terminal]
           ,[ContParinte]
           ,[EsteAnalitic]
           ,[DebitInitial]
           ,[CreditInitial]
           ,[DebitPrecedent]
           ,[CreditPrecedent]
           ,[Categorie]
           ,[RulajDebit]
           ,[RulajCredit]
           ,[Sold])
           
     SELECT RTRIM(LTRIM(@Cont)) +'.' + RTRIM(LTRIM([ArtAl])) AS [Cont]
      ,aa.[CodUnitate]
      ,aa.[Denumire]
      ,cc.Tip
      ,aa.[Terminal]
      ,RTRIM(LTRIM('101')) + CASE WHEN RTRIM(LTRIM([AAParinte]))='N/A' THEN '' ELSE '.' +RTRIM(LTRIM([AAParinte])) END  AS [ContParinte]
      ,1 AS [EsteAnalitic]
      ,0,0,0,0
      ,cc.[Categorie]
      ,0,0,0
	 FROM [ArticoleAliniate] aa JOIN [Conturi] cc ON aa.CodUnitate=cc.CodUnitate AND cc.Cont=@cont AND cc.CodUnitate=@CodUnitate
	 WHERE aa.CodUnitate = @CodUnitate AND [Categorii] like '%[CLT]%'  AND [ArtAl]<>'N/A'
	 AND NOT EXISTS(SELECT * FROM [Conturi] WHERE [CodUnitate]=@CodUnitate AND 
	 [CONT]=RTRIM(LTRIM(@Cont)) +'.' + RTRIM(LTRIM(aa.[ArtAl])))
	 
	 
END
GO


IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[AreArticoleAliniateGenerate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[AreArticoleAliniateGenerate]
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[AreArticoleAliniateGenerate]
(
	-- Add the parameters for the function here
	@Cont TipCont,
	@CodUnitate smallint
)
RETURNS bit
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar int
	SET @ResultVar=0
	-- Add the T-SQL statements to compute the return value here
	 select @ResultVar =COUNT(*) from [Conturi] c JOIN [ArticoleAliniate] a ON c.CodUnitate=a.CodUnitate
	 AND [Cont]=LTRIM(RTRIM(@Cont)) + '.'+[artAl]
	 WHERE c.CodUnitate=@CodUnitate AND a.CodUnitate=@CodUnitate
	
	
	-- Return the result of the function
	RETURN CASE WHEN @ResultVar <> 0 THEN 1 ELSE 0 END

END

GO




IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[exBug_ConturiSpeciale]'))
DROP VIEW [dbo].[exBug_ConturiSpeciale]
GO


CREATE VIEW [dbo].[exBug_ConturiSpeciale]
AS
SELECT     Cont, CodUnitate, Tipuri, Descriere, dbo.AreArticoleAliniateGenerate(Cont, CodUnitate) AS AreArticoleGen
FROM         dbo.ConturiSpeciale

GO

IF NOT  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[AngajamentLegalNC]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AngajamentLegalNC](
	[IDAL] [int] NOT NULL,
	[IDDocument] [bigint] NOT NULL,
 CONSTRAINT [PK_AngajamentLegalNC] PRIMARY KEY CLUSTERED 
(
	[IDAL] ASC,
	[IDDocument] ASC
) ON [PRIMARY],
CONSTRAINT [FK_AngajamentLegalNC_AngajamentLegal] FOREIGN KEY([IDAL]) REFERENCES [dbo].[AngajamentLegal] ([IDAL]),
CONSTRAINT [FK_AngajamentLegalNC_RulajeDoc] FOREIGN KEY([IDDocument]) REFERENCES [dbo].[RulajeDoc] ([IDDocument])
) ON [PRIMARY]

END
GO




/****** Object:  StoredProcedure [dbo].[GenNotaAB]    Script Date: 05/22/2011 13:43:00 ******/
IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[GenNotaAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GenNotaAL]
GO

CREATE PROCEDURE [dbo].[GenNotaAL] 
	-- Add the parameters for the stored procedure here
	@IDAL int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @NrDocAdd int
	DECLARE @LastNrDoc bigint
	SET NOCOUNT ON;

    BEGIN TRAN

	DELETE FROM [Rulaje]  FROM [AngajamentLegalNC] a
	WHERE [IDAL] = @IDAL AND [Rulaje].[IDDocument] = a.[IDDocument]
	IF @@ERROR <>0  GOTO EroareStergere

	DELETE FROM [RulajeDoc]  FROM [AngajamentLegalNC] a
	WHERE [IDAL] = @IDAL AND [RulajeDoc].[IDDocument] = a.[IDDocument]
	IF @@ERROR <>0  GOTO EroareStergere

	DELETE FROM [AngajamentLegalNC] 
	WHERE [IDAL] = @IDAL
	IF @@ERROR <>0  GOTO EroareStergere


	INSERT INTO [RulajeDoc]
	([ValoareLei],[ValoareValuta],[NumarDoc],[DataDoc],[NumarNota],[TipFormula],[IDJurnal]
	,[Observatii],[xUser],[xHost],[CodUnitate])   
	SELECT
	[SumaTotala] AS ValoareLei,
	0 AS ValoareValuta,
	Numar AS NumarDoc,
	Data AS DataDoc,
	'ALG'+LTRIM(RTRIM(CAST(Numar AS varchar(20)))) AS NumarNota,
	3 AS TipFormula, --monopartida
	IdJurnal,
	'Nota Contabila pentru AL nr. ' + CAST(Numar AS varchar(20)) AS Observatii,
	system_user AS xUser
	,host_name() AS xHost,
	CodUnitate
	FROM [AngajamentLegal] a JOIN 
	(SELECT DISTINCT IDJurnal FROM[AngajamentLegalDetaliu] b WHERE b.[IDAL]=@IDAL) b ON  a.[IDAL]=@IDAL 
	IF @@ERROR <>0  GOTO EroareDoc

	INSERT INTO [Rulaje]
		([ContDebit],[ContCredit],[CodValuta],[IDDocument] ,[ValoareValuta],[Curs],[ValoareLei],[DataDocJust]
			,[TipDocJust] ,[NumarDocJust] ,[ExplicatiiDocJust] ,[NrCrt],[CodUnitate])
	SELECT 
	CASE WHEN [Suma] > 0 THEN ebs.[ContAB]+'.' + abd.[ArtAl] ELSE 'N/A' END ContDebit,
	CASE WHEN [Suma] < 0 THEN 'N/A' ELSE ebs.[ContAB]+'.' + abd.[ArtAl] END ContCredit,
	0 AS CodValuta,
	rd.[IDDocument] AS IDDocument,
	0 AS ValoareValuta,
	0 AS Curs,
	Suma,
	ab.[Data] AS DataDocJust,
	'AL' AS TipDocJust,
	ab.[Numar] AS NumarDocJust,
	'Angajament Legal'  AS ExplicatiiDocJust,
	0 AS NrCrt,
	ab.[CodUnitate]
	FROM [AngajamentLegalDetaliu] abd JOIN
	[AngajamentLegal] ab ON ab.[IDAL] = abd.[IDAL] JOIN
	[ExBugSetup] ebs ON ebs.[CodUnitate] = ab.[CodUnitate] JOIN
	[RulajeDoc] rd ON 'ALG'+LTRIM(RTRIM(CAST(ab.Numar AS varchar(20))))= rd.[NumarNota]
	WHERE ab.[IDAL]=@IDAL		
	IF @@ERROR <>0  GOTO EroareRul

	UPDATE [RulajeDoc] SET [ValoareLei] = (SELECT SUM([ValoareLei]) FROM [Rulaje] r WHERE r.[IDDocument] = [RulajeDoc].[IDDocument])	
	FROM [AngajamentLegal] ab 
	WHERE ab.[IDAL] = @IDAL AND 'ALG'+LTRIM(RTRIM(CAST(ab.Numar AS varchar(20))))= [NumarNota]
	IF @@ERROR <>0  GOTO EroareDocSuma

	INSERT INTO [AngajamentLegalNC] ([IDAL],[IDDocument])
	SELECT [IDAL],[IDDocument] FROM [AngajamentLegal] ab JOIN [RulajeDoc] rd
	ON ab.[IDAL] = @IDAL AND 'ALG'+LTRIM(RTRIM(CAST(ab.Numar AS varchar(20))))= rd.[NumarNota]
	IF @@ERROR <>0  GOTO EroareAB

	COMMIT TRAN
	GOTO Gata
	EroareDoc:
		ROLLBACK
		RAISERROR('Eroare la generarea documentului ',16,-1)
		return
	EroareAB:
		ROLLBACK
		RAISERROR('Eroare la generarea legaturilor intre documente ',16,-1)
		return
	EroareDocSuma:
		ROLLBACK
		RAISERROR('Eroare la actualizarea sumei pe nota contabila ',16,-1)
		return
	EroareRul:
		ROLLBACK
		RAISERROR('Eroare la generarea rulajului ',16,-1)
		return
	EroareStergere:
		ROLLBACK
		RAISERROR('Eroare la stergerea notei anterioare ',16,-1)
		return

	Gata:
END

GO


/****** Object:  StoredProcedure [dbo].[exbug_FiltreazaOrdonantari]    Script Date: 05/22/2011 22:31:11 ******/
IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[exbug_FiltreazaOrdonantari]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_FiltreazaOrdonantari]
GO

CREATE PROCEDURE [dbo].[exbug_FiltreazaOrdonantari] 
	-- Add the parameters for the stored procedure here
	@filtru varchar(500),
	@an varchar(4)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [Ordonantari'+@an+'] where ' +@filtru 
	EXEC(@sel)
END
GO


IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[ActSolduri]'))
DROP TRIGGER [dbo].[ActSolduri]
GO


CREATE TRIGGER [dbo].[ActSolduri] ON [dbo].[Conturi] 
FOR INSERT, UPDATE,DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @Cont AS varchar(50)
DECLARE @ContParinte AS varchar(50)
DECLARE @CodUnitate AS smallint

SELECT @Cont = [Cont],@CodUnitate=[CodUnitate],@ContParinte=[ContParinte] FROM inserted
IF UPDATE(ContParinte)
BEGIN
	UPDATE [Conturi] SET Terminal=0 FROM inserted i WHERE [Conturi].[Cont] = i.[ContParinte] AND [Conturi].[CodUnitate]=i.[CodUnitate] 
		AND ([Conturi].[Cont]<>'N/A')
END
IF UPDATE(CreditInitial) OR UPDATE(DebitInitial) OR EXISTS(SELECT * FROM deleted)
BEGIN
	WHILE @Cont<>'N/A'
	BEGIN
    --modifica debitul initial 
		UPDATE [Conturi]  SET [Conturi].[DebitInitial] = (SELECT SUM(a.DebitInitial-a.CreditInitial) FROM [Conturi]a WHERE  a.[ContParinte]=@ContParinte AND a.[CodUnitate]=@CodUnitate),[Conturi].[CreditInitial]= 0
		FROM inserted i  WHERE [Conturi].[Cont] = @ContParinte AND [Conturi].[CodUnitate]=@CodUnitate AND
			 ([Conturi].[Cont]<>'N/A')

		UPDATE [Conturi]  SET [Conturi].[CreditInitial] = -[Conturi].[DebitInitial], [Conturi].[DebitInitial]=0
		FROM inserted i  WHERE [Conturi].[Cont] = @ContParinte AND [Conturi].[CodUnitate]=@CodUnitate AND
			 ([Conturi].[Tip]='P') AND ([Conturi].[Cont]<>'N/A') 
			 
		UPDATE [Conturi]  SET [Conturi].[CreditInitial] = -[Conturi].[DebitInitial], [Conturi].[DebitInitial]=0
		FROM inserted i  WHERE [Conturi].[Cont] = @ContParinte AND [Conturi].[CodUnitate]=@CodUnitate AND
			 ([Conturi].[Tip]='B')  AND [Conturi].[DebitInitial]<0 AND ([Conturi].[Cont]<>'N/A')
			 
		SELECT @Cont=[ContParinte],@ContParinte=[ContParinte] FROM [Conturi] WHERE [Cont]=@Cont AND [CodUnitate]=@CodUnitate	 		 
	END	 
		 
END
END

GO



IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[GenNotaAB]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GenNotaAB]
GO


CREATE PROCEDURE [dbo].[GenNotaAB] 
	-- Add the parameters for the stored procedure here
	@IDAB int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @NrDocAdd int
	DECLARE @LastNrDoc bigint
	SET NOCOUNT ON;

    BEGIN TRAN

	Print('Sterge rulaje')
	DELETE FROM [Rulaje]  FROM [AngajamentBugetarNC] a
	WHERE [IDAB] = @IDAB AND [Rulaje].[IDDocument] = a.[IDDocument]
	IF @@ERROR <>0  GOTO EroareStergere
	
	Print('Sterge [AngajamentBugetarNC]')
	DELETE FROM [AngajamentBugetarNC] 
	WHERE [IDAB] = @IDAB
	IF @@ERROR <>0  GOTO EroareStergere
	
	Print('Sterge rulajeDoc')
	Print(@IDAB)
	DELETE FROM [RulajeDoc]  FROM AngajamentBugetar a
	WHERE [IDAB] = @IDAB AND  'ABG'+LTRIM(RTRIM(CAST(Numar AS varchar(20)))) + '[' + LTRIM(RTRIM(CAST(@IDAB AS varchar(20)))) +']'= [RulajeDoc].[NumarNota]
	
	
	IF @@ERROR <>0  GOTO EroareStergere

	

	Print('Insert rulajeDoc')
	INSERT INTO [RulajeDoc]
	([ValoareLei],[ValoareValuta],[NumarDoc],[DataDoc],[NumarNota],[TipFormula],[IDJurnal]
	,[Observatii],[xUser],[xHost],[CodUnitate])   
	SELECT
	[SumaTotala] AS ValoareLei,
	0 AS ValoareValuta,
	Numar AS NumarDoc,
	Data AS DataDoc,
	'ABG'+LTRIM(RTRIM(CAST(Numar AS varchar(20)))) + '[' + LTRIM(RTRIM(CAST(@IDAB AS varchar(20)))) +']' AS NumarNota,
	3 AS TipFormula, --monopartida
	IdJurnal,
	'Nota Contabila pentru AL nr. ' + CAST(Numar AS varchar(20)) AS Observatii,
	system_user AS xUser
	,host_name() AS xHost,
	CodUnitate
	FROM [AngajamentBugetar] a JOIN 
	(SELECT DISTINCT IDJurnal FROM[AngajamentBugetarDetaliu] b WHERE b.[IDAB]=@IDAB) b ON  a.[IDAB]=@IDAB 
	IF @@ERROR <>0  GOTO EroareDoc
	
	PRINT('Insert Rulaje')

	INSERT INTO [Rulaje]
		([ContDebit],[ContCredit],[CodValuta],[IDDocument] ,[ValoareValuta],[Curs],[ValoareLei],[DataDocJust]
			,[TipDocJust] ,[NumarDocJust] ,[ExplicatiiDocJust] ,[NrCrt],[CodUnitate])
	SELECT 
	CASE WHEN [Suma] > 0 THEN ebs.[ContAB]+'.' + abd.[ArtAl] ELSE 'N/A' END ContDebit,
	CASE WHEN [Suma] < 0 THEN 'N/A' ELSE ebs.[ContAB]+'.' + abd.[ArtAl] END ContCredit,
	0 AS CodValuta,
	rd.[IDDocument] AS IDDocument,
	0 AS ValoareValuta,
	0 AS Curs,
	Suma,
	ab.[Data] AS DataDocJust,
	'AB' AS TipDocJust,
	ab.[Numar] AS NumarDocJust,
	'Angajament Bugetar'  AS ExplicatiiDocJust,
	0 AS NrCrt,
	ab.[CodUnitate]
	FROM [AngajamentBugetarDetaliu] abd JOIN
	[AngajamentBugetar] ab ON ab.[IDAB] = abd.[IDAB]  JOIN
	[ExBugSetup] ebs ON ebs.[CodUnitate] = ab.[CodUnitate] JOIN
	[RulajeDoc] rd ON 'ABG'+LTRIM(RTRIM(CAST(Numar AS varchar(20)))) + '[' + LTRIM(RTRIM(CAST(@IDAB AS varchar(20)))) +']'= rd.[NumarNota] AND rd.IDJurnal=abd.IdJurnal
	WHERE ab.[IDAB]=@IDAB		
	IF @@ERROR <>0  GOTO EroareRul

	UPDATE [RulajeDoc] SET [ValoareLei] = (SELECT SUM([ValoareLei]) FROM [Rulaje] r WHERE r.[IDDocument] = [RulajeDoc].[IDDocument])	
	FROM [AngajamentBugetar] ab 
	WHERE ab.[IDAB] = @IDAB AND 'ABG'+LTRIM(RTRIM(CAST(Numar AS varchar(20)))) + '[' + LTRIM(RTRIM(CAST(IDAB AS varchar(20)))) +']'= [NumarNota]
	IF @@ERROR <>0  GOTO EroareDocSuma

	INSERT INTO [AngajamentBugetarNC] ([IDAB],[IDDocument])
	SELECT [IDAB],[IDDocument] FROM [AngajamentBugetar] al JOIN [RulajeDoc] rd
	ON al.[IDAB] = @IDAB AND 'ABG'+LTRIM(RTRIM(CAST(Numar AS varchar(20)))) + '[' + LTRIM(RTRIM(CAST(@IDAB AS varchar(20)))) +']'= rd.[NumarNota]
	IF @@ERROR <>0  GOTO EroareAB

	COMMIT TRAN
	GOTO Gata
	EroareDoc:
		ROLLBACK
		RAISERROR('Eroare la generarea documentului ',16,-1)
		return
	EroareAB:
		ROLLBACK
		RAISERROR('Eroare la generarea legaturilor intre documente ',16,-1)
		return
	EroareDocSuma:
		ROLLBACK
		RAISERROR('Eroare la actualizarea sumei pe nota contabila ',16,-1)
		return
	EroareRul:
		ROLLBACK
		RAISERROR('Eroare la generarea rulajului ',16,-1)
		return
	EroareStergere:
		ROLLBACK
		RAISERROR('Eroare la stergerea notei anterioare ',16,-1)
		return

	Gata:
END



GO



IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[PersoaneVizaCFP]'))
DROP VIEW [dbo].[PersoaneVizaCFP]
GO


CREATE VIEW [dbo].[PersoaneVizaCFP]
AS
SELECT     dbo.Persoane.NumePrenume, dbo.ResponsabilVizaCFP.CodUnitate, dbo.ResponsabilVizaCFP.DataActivare, dbo.ResponsabilVizaCFP.IDResponsabil, 
                      dbo.ResponsabilVizaCFP.IDPersoana
FROM         dbo.ResponsabilVizaCFP INNER JOIN
                      dbo.Persoane ON dbo.ResponsabilVizaCFP.IDPersoana = dbo.Persoane.IdPersoana
GO



IF  EXISTS (SELECT * FROM sysobjects WHERE id =  OBJECT_ID(N'[dbo].[GetBalanta]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetBalanta]
GO


CREATE PROCEDURE [dbo].[GetBalanta] 
	-- Add the parameters for the stored procedure here
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@pJurnal int,
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@An smallint,
	@Tgc bit
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
DECLARE @CodUnitate smallint
DECLARE @cmd varchar(5000)
DECLARE @strCodUnitate varchar(20)
DECLARE @DtDataStartUI varchar(20)
DECLARE @DtDataEndUI varchar(20)
DECLARE @strCategorie varchar(100)
DECLARE @strAn varchar(10)

SET @CodUnitate=0
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
SET @strCodUnitate = cast(@CodUnitate AS varchar(20))

DECLARE @ConturiTGC AS varchar(2000)
SET @ConturiTGC = ','
SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

DECLARE @ConturiSS AS varchar(2000)
SET @ConturiSS = ','
SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate



SET @DtDataStartUI = cast(YEAR(@pDtDataStartUI) as varchar(4)) + '-' +cast(MONTH(@pDtDataStartUI) as varchar(2)) + '-' + cast(DAY(@pDtDataStartUI) as varchar(2))
SET @DtDataEndUI = cast(YEAR(@pDtDataEndUI) as varchar(4)) + '-' +cast(MONTH(@pDtDataEndUI) as varchar(2)) + '-' + cast(DAY(@pDtDataEndUI) as varchar(2))

SET @strCategorie = LTRIM(RTRIM(cast(@pCategorie as varchar(10))))
SET @strAn = cast(@An as varchar(10))

IF @Tgc=1
	SET @strCategorie = @strCategorie + ',3'

SET @cmd = 'SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
    DebPrecCalc [RulPrecDeb], CrePrecCalc [RulPrecCre], 
    DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
	DebInit + DebPrecCalc+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc + COALESCE(CreCurent, 0) RulTotalCre, 
	dbo.SoldDebit(Tip, DebInit + DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalDeb, 
	dbo.SoldCredit(Tip, DebInit + DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalCre
	FROM
	(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit], 
	sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc,  
	sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
	FROM '
IF @Tgc=1
BEGIN
	DECLARE @cmdCont varchar(2000)
	SET @cmdCont =  
	'(SELECT a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue],a.[DebitPrecedent],a.[CreditPrecedent],
	sum(b.DebitInitial) [DebitInitial] ,sum(b.creditInitial) [CreditInitial]
	FROM [Conturi] a JOIN [Conturi] b
	ON dbo.IsContInclus(a.cont,b.cont,a.Terminal)=1 and a.codunitate=b.codunitate
	where a.[CodUnitate]=' + @strCodUnitate +' and a.categorie=3 and b.ContParinte=''N/A''
	group by a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue],a.[DebitPrecedent],a.[CreditPrecedent]
	UNION
	SELECT  a2.[Cont],a2.[CodUnitate],a2.[Denumire],a2.[Tip],a2.[Terminal],a2.[ContParinte],a2.[EsteAnalitic],a2.[Categorie],a2.[SortValue],a2.[DebitPrecedent],a2.[CreditPrecedent],
	a2.[DebitInitial] ,a2.[CreditInitial] FROM [Conturi] a2
	WHERE a2.codunitate='+@strCodUnitate +' and a2.categorie=0 )'
	IF @An <> 0 
		SET @cmdCont = REPLACE(@cmdCont,'[Conturi]', '[Conturi' + @strAn + ']')
	
	SET @cmd = @cmd  + @cmdCont
END
ELSE
BEGIN
	IF @An <> 0 
		SET @cmd = @cmd  + '[Conturi' + @strAn + ']'
	ELSE
		SET @cmd = @cmd  + '[Conturi]'
END

SET @cmd = @cmd  + ' c
    LEFT OUTER JOIN 
	(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
	   sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
			(SELECT 			    
				coalesce(SUM(CASE WHEN D.DataDoc<''' + @DtDataStartUI + ''' THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
				coalesce(SUM(CASE WHEN D.DataDoc>=''' + @DtDataStartUI +''' AND D.DataDoc<=''' + @DtDataEndUI +''' THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
				0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
				F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
				FROM '
--[RulajeDoc]
IF @An <> 0 
	SET @cmd = @cmd  + '[RulajeDoc' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[RulajeDoc]'

SET @cmd = @cmd  + ' D INNER JOIN '
--[Rulaje]
IF @An <> 0 
	SET @cmd = @cmd  + '[Rulaje' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[Rulaje]'


SET @cmd = @cmd  + ' F ON D.[IDDocument] = F.[IDDocument] 
				WHERE  d.[CodUnitate] =' +  @strCodUnitate
			
IF 	@pJurnal<>-1	
	SET @cmd = @cmd + 	' AND D.IDJurnal= ' + cast(@pJurnal as varchar(10)) 
					
SET @cmd = @cmd  + ' GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
			UNION
			SELECT 
				0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
				coalesce(SUM(CASE WHEN D.DataDoc < ''' + @DtDataStartUI+''' THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
				coalesce(SUM(CASE WHEN D.DataDoc >=''' + @DtDataStartUI+''' AND D.DataDoc<=''' + @DtDataEndUI +''' THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
                F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
				FROM '
--[RulajeDoc]
IF @An <> 0 
	SET @cmd = @cmd  + '[RulajeDoc' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[RulajeDoc]'

SET @cmd = @cmd  + ' D INNER JOIN '
--[Rulaje]
IF @An <> 0 
	SET @cmd = @cmd  + '[Rulaje' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[Rulaje]'

SET @cmd = @cmd  + ' F ON D.[IDDocument] = F.[IDDocument] 
				WHERE d.[CodUnitate] = '+@strCodUnitate 
IF 	@pJurnal<>-1	
	SET @cmd = @cmd + 	' AND D.IDJurnal= ' + cast(@pJurnal as varchar(10)) 
			

SET @cmd = @cmd +  ' GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal) t1
	GROUP BY Cont,CodUnitate,IDJurnal
	) RUL 
	ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
	WHERE  c.[Cont]<>''N/A'' AND Categorie in ('+ @strCategorie + ') AND c.[CodUnitate] ='+ @strCodUnitate + '
	AND c.Cont >= '''+@pStrContStart + ''''
IF @pStrContEnd	<> ''
	SET @cmd = @cmd + ' AND c.Cont <='''+ @pStrContEnd +''''
	
SET @cmd = @cmd +  'GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
	) [balanta] '

DECLARE @cmdFilt varchar(8000)
SET @cmdfilt = ' WHERE ' 
IF @pCfm <> 1
BEGIN
	IF @An <> 0 
		SET @cmdfilt  = @cmdfilt +  ' (EXISTS (SELECT ContDebit, ContCredit FROM Rulaje'+@strAn+' WHERE ((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) AND [CodUnitate]='+@strCodUnitate+') OR ((DebInit<>0) OR (CreInit<>0)))  '
	ELSE
		SET @cmdfilt  = @cmdfilt +  ' (EXISTS (SELECT ContDebit, ContCredit FROM Rulaje WHERE ((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) AND [CodUnitate]='+@strCodUnitate+') OR ((DebInit<>0) OR (CreInit<>0)))  '
END

IF @pAnalitica <> 1
BEGIN
	IF @pCfm <> 1
		SET @cmdfilt  = @cmdfilt +  ' AND '
	IF @Tgc=0
		SET @cmdfilt  = @cmdfilt +  ' (([EsteAnalitic]=0)  OR (dbo.IsContIn('',''+ Cont+'','','''+@ConturiSS+''')<>0)) '
	ELSE
		SET @cmdfilt  = @cmdfilt +  ' (([EsteAnalitic]=0) OR (dbo.IsContIn('',''+ Cont+'','','''+@ConturiTGC+''')<>0)) '

END
IF @pCfmRulaje <> 1
BEGIN
	IF (@pCfm <> 1) OR (@pAnalitica <> 1)
		SET @cmdfilt  = @cmdfilt +  ' AND '
	SET @cmdfilt  = @cmdfilt +  ' (DebCurent<>0 OR CreCurent<>0) '
END

IF @pTerminal <> 0
BEGIN
	IF (@pCfm <> 1) OR (@pAnalitica <> 1) OR (@pCfmRulaje <> 1)
		SET @cmdfilt  = @cmdfilt +  ' AND '
	SET @cmdfilt  = @cmdfilt +  '([Terminal]=1)'
END
IF @cmdfilt <> ' WHERE '
	SET @cmd  = @cmd +  @cmdFilt
SET @cmd  = @cmd +  ' ORDER BY SortValue '
PRINT(@cmdFilt)
PRINT(@cmd)
EXEC(@cmd) 
IF @@ERROR != 0 GOTO EROARE  GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare balanta!',16,-1)  GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	GOTO Gata
Gata:

END


GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ExBugSetup' AND COLUMN_NAME = 'TiparesteContPeOrdonantare')
BEGIN

ALTER TABLE dbo.ExBugSetup ADD TiparesteContPeOrdonantare bit NULL DEFAULT 0
END
GO


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Conturi' AND COLUMN_NAME = 'SortValue')
BEGIN

ALTER TABLE dbo.Conturi DROP COLUMN SortValue
END
GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[GetOrderString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[GetOrderString]
GO

CREATE FUNCTION [dbo].[GetOrderString] 
(
	@Cont_par TipCont	
)
RETURNS varchar(100)
AS
BEGIN
	DECLARE @Cont TipCont
	SET @Cont = LTRIM(RTRIM(@Cont_par))
	DECLARE @Ret TipCont 
	DECLARE @LenC int
	SET @Ret=@Cont 
	IF CHARINDEX('.',@cont)<1 
		BEGIN 
		SET @LenC = 0
		IF LEN(@Cont)< 10
			SET @LenC = 10-LEN(@Cont)
		SET @Ret = RIGHT(@Cont, 10-@LenC) + REPLICATE('0', @LenC) +	REPLICATE('0', 90)
		END
	ELSE
		BEGIN
			DECLARE @pointPoz int
			DECLARE @bucCont varchar(100)
			--numara cate puncte are
			DECLARE @i int,@Cate int
			SET @i=1
			SET @Cate=10
			SET @Ret = ''	
			SET @pointPoz = CHARINDEX('.',@Cont)
			WHILE (@pointPoz < LEN(@Cont)) AND (@pointPoz>0) AND @i<10
			BEGIN
				SET @LenC = 0
				IF @pointPoz< 11
					SET @LenC =  11 - @pointPoz
				IF @i=1
					SET @bucCont = RIGHT(SUBSTRING(@Cont,1,@pointPoz-1),10-@LenC) + REPLICATE('0',@LenC)	
				ELSE
					SET @bucCont = REPLICATE('0',@LenC)	+ RIGHT(SUBSTRING(@Cont,1,@pointPoz-1),10-@LenC)
				SET @Cont=SUBSTRING(@Cont,@pointPoz+1,LEN(@Cont))
				SET @pointPoz = CHARINDEX('.',@Cont)
				SET @Ret = @Ret + @bucCont
				SET @i=@i+1
			END
			SET @LenC = 0
			IF LEN(@Cont)< 10
				SET @LenC = 10-LEN(@Cont)
			SET @Ret = @Ret + REPLICATE('0',@LenC) + RIGHT(@Cont, 10-@LenC)  + REPLICATE('0', (10-@i)*10)
		END

	

	-- Return the result of the function
	RETURN @Ret

END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Conturi' AND COLUMN_NAME = 'SortValue')
BEGIN
	ALTER TABLE dbo.Conturi ADD SortValue  AS ([dbo].[GetOrderString]([Cont]))
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Persoane' AND COLUMN_NAME = 'MotivIncetareContract')
BEGIN
	ALTER TABLE dbo.Persoane ADD MotivIncetareContract varchar(250) NULL
END
GO


IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[admin_Liste]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[admin_Liste](
		[IDLista] [smallint] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](50) NOT NULL,
		[Titlu] [varchar](150) NOT NULL,
	 CONSTRAINT [PK_admin_Liste] PRIMARY KEY CLUSTERED 
	(
		[IDLista] ASC
	) ON [PRIMARY]
	) ON [PRIMARY]

	SET IDENTITY_INSERT [admin_Liste] ON
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(1,'Balanta','Balanta')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(2,'Fisa contului','Fisa contului')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(3,'Cartea mare','Cartea mare')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(4,'Registrul jurnal','Registrul jurnal')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(5,'Registrul de casa','Registrul de casa')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(6,'Extras de cont','Extras de cont')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(7,'Fise generale','Fise generale')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(8,'Bilant','Bilant')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(9,'Contul de profit si pierdere','Contul de profit si pierdere')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(10,'Operatiuni diverse','Operatiuni diverse')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(11,'Extras de cont centralizat','Extras de cont centralizat')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(12,'Lista operatiunilor bugetare','Lista operatiunilor bugetare')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(13,'Executia cheltuielilor','Executia cheltuielilor')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(14,'Cont de rezultat patrimonial','Cont de rezultat patrimonial')
	INSERT INTO [admin_liste] ([IDLista],[Denumire],[Titlu])VALUES(15,'Situatia veniturilor si cheltuielilor','Situatia veniturilor si cheltuielilor')
	SET IDENTITY_INSERT [admin_Liste] OFF
END
GO

IF  NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[SemnaturiListe]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[SemnaturiListe](
		[IDLista] [smallint] NOT NULL,
		[IDSemnatura] [smallint] NOT NULL,
		[TipLista] [smallint] NOT NULL,
	 CONSTRAINT [PK_SemnaturiListe] PRIMARY KEY CLUSTERED 
	(
		[IDLista] ASC,
		[IDSemnatura] ASC,
		[TipLista] ASC
	) ON [PRIMARY],
	CONSTRAINT [FK_SemnaturiListe_Semnaturi] FOREIGN KEY([IDSemnatura])  REFERENCES [dbo].[Semnaturi] ([IdSemnatura]),
	CONSTRAINT [FK_admin_Liste_Semnaturi] FOREIGN KEY([IDLista])  REFERENCES [dbo].[admin_Liste] ([IdLista])
	) ON [PRIMARY]
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'BalConSetup' AND COLUMN_NAME = 'LiniiSemnatura')
BEGIN
	ALTER TABLE dbo.BalConSetup ADD LiniiSemnatura bit DEFAULT 1 NULL
END
GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[CalcDisponibilPAC]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CalcDisponibilPAC]
GO

CREATE PROCEDURE [dbo].[CalcDisponibilPAC]
	@CodUnitate smallint,
	@IDPAC int,
	@Data datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE [PropunereAngajareCheltuialaDetaliu] SET [CredBugAprob]= 
	(SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate
	 WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc rd  WHERE
		IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate AND DataDoc<=@Data)) AND ContDebit= es.ContAB + '.'+[PropunereAngajareCheltuialaDetaliu].[ArtAl])
		-(SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
		IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate AND DataDoc<=@Data)) AND ContCredit= es.ContAB + '.'+[PropunereAngajareCheltuialaDetaliu].[ArtAl])
		WHERE [IDPac]=@IDPAC

	UPDATE [PropunereAngajareCheltuialaDetaliu] SET [CredBugAngaj]= (SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] 
	r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
		IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate  AND DataDoc<=@Data)) AND ContDebit= es.ContAL +'.'+[PropunereAngajareCheltuialaDetaliu].[ArtAl])
		-(SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
		IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate  AND DataDoc<=@Data)) AND ContCredit= es.ContAL + '.'+[PropunereAngajareCheltuialaDetaliu].[ArtAl]) +
		(SELECT COALESCE(sum(Suma),0) FROM [PropunereAngajareCheltuialaDetaliu]a  WHERE a.[IDPACDetaliu] < [PropunereAngajareCheltuialaDetaliu].[IDPACDetaliu] AND a.[IDPAC]=@IDPAC)
		WHERE [IDPac]=@IDPAC
		
		
	UPDATE [PropunereAngajareCheltuialaDetaliu] SET [Clasif]=LTRIM(RTRIM(j.[Codificare]))+'.' +dbo.GetTitlu([ArtAl])+'.' + LTRIM(RTRIM([ArtAl]))  FROM [Jurnale] j WHERE j.[IDJurnal] = [PropunereAngajareCheltuialaDetaliu] .[IDJurnal] AND [IDPAC] =@IDPAC
END


GO


IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[exbug_GenereazaPACsiAB]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_GenereazaPACsiAB]
GO

CREATE PROCEDURE [dbo].[exbug_GenereazaPACsiAB]
	-- Add the parameters for the stored procedure here
	@IDDocument bigint,
	@CodUnitate smallint,
	@IDPAC int OUT,
	@IDAB int OUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @IDResponsabil int
	SET @IDResponsabil = 0
	
	SELECT TOP 1 @IDResponsabil = [IDResponsabil] FROM ResponsabilVizaCFP WHERE [CodUnitate] = @CodUnitate
	IF @IDResponsabil = 0
		GOTO Eroare

	--Propunere angajare cheltuiala
    INSERT INTO [PropunereAngajareCheltuiala]
           ([Numar]
           ,[Data]
           ,[Compartiment]
           ,[Scopul]
           ,[Beneficiarul]
           ,[CodUnitate]
           ,[SumaTotala]
           ,[VizatCFP]
           ,[MotivRefuz]
           ,[IDResponsabil]
           ,[DataVizaCFP]
           ,[NrRegCFP]
           ,[OrdonatorCredite])
     SELECT CAST(NumarDoc as varchar(20)) [Numar],
		[DataDoc],
		[CompartimentSpecialitate],
		COALESCE([Observatii],''),
		[Linia1Stanga],
		rd.[CodUnitate],
		[ValoareLei],
		1,'',@IDResponsabil,GETDATE(),'',[OrdonatorCredite]
      FROM [RulajeDoc] rd JOIN ExBugSetup ebs ON rd.CodUnitate = ebs.CodUnitate
       JOIN BalconSetup bcs ON bcs.CodUnitate = rd.CodUnitate
			 WHERE [IDDocument]=@IDDocument 
           
	SET @IDPAC= SCOPE_IDENTITY()

	INSERT INTO [PropunereAngajareCheltuialaDetaliu]
           ([IDPac]
           ,[IDJurnal]
           ,[ArtAl]
           ,[Suma]
           ,[CredBugAprob]
           ,[CredBugAngaj]
           ,[Clasif])
     SELECT @IDPAC,sd.IDJurnal,REPLACE(ContDebit, ContAB+'.','') ArtAl ,
     rl.valoareLei,0,0,'' FROM 
     Rulaje rl JOIN [RulajeDoc] sd ON sd.IDDocument=rl.IDDocument
     JOIN ExBugSetup ebs ON ebs.CodUnitate = rl.CodUnitate
		WHERE rl.[IDDocument]=@IDDocument
		AND (rl.ContDebit like ContAB +'%')

	INSERT INTO [PropunereAngajareCheltuialaDetaliu]
           ([IDPac]
           ,[IDJurnal]
           ,[ArtAl]
           ,[Suma]
           ,[CredBugAprob]
           ,[CredBugAngaj]
           ,[Clasif])
     SELECT @IDPAC,sd.IDJurnal,REPLACE(ContCredit, ContAB+'.','') ArtAl ,
     rl.valoareLei,0,0,'' FROM 
     Rulaje rl JOIN [RulajeDoc] sd ON sd.IDDocument=rl.IDDocument
     JOIN ExBugSetup ebs ON ebs.CodUnitate = rl.CodUnitate
		WHERE rl.[IDDocument]=@IDDocument
		AND (rl.ContCredit like ContAB +'%')  


	--Angajament bugetar
    INSERT INTO [AngajamentBugetar]
           ([Numar]
           ,[Data]
           ,[Compartiment]
           ,[Scopul]
           ,[Beneficiarul]
           ,[CodUnitate]
           ,[SumaTotala]
           ,[VizatCFP]
           ,[MotivRefuz]
           ,[IDResponsabil]
           ,[DataVizaCFP]
           ,[NrRegCFP]
           ,[OrdonatorCredite],
           [TipAngajament])
     SELECT CAST(NumarDoc as varchar(20)) [Numar],
		[DataDoc],
		[CompartimentSpecialitate],
		COALESCE([Observatii],''),
		[Linia1Stanga],
		rd.[CodUnitate],
		[ValoareLei],
		1,'',@IDResponsabil,GETDATE(),'',[OrdonatorCredite],0
      FROM [RulajeDoc] rd JOIN ExBugSetup ebs ON rd.CodUnitate = ebs.CodUnitate
       JOIN BalconSetup bcs ON bcs.CodUnitate = rd.CodUnitate
			 WHERE [IDDocument]=@IDDocument 
           
	SET @IDAB= SCOPE_IDENTITY()

	INSERT INTO [AngajamentBugetarDetaliu]
           ([IDAB]
           ,[IDJurnal]
           ,[ArtAl]
           ,[Suma]
           ,[Clasif])
     SELECT @IDAB,sd.IDJurnal,REPLACE(ContDebit, ContAB+'.','') ArtAl ,
     rl.valoareLei,'' FROM 
     Rulaje rl JOIN [RulajeDoc] sd ON sd.IDDocument=rl.IDDocument
     JOIN ExBugSetup ebs ON ebs.CodUnitate = rl.CodUnitate
		WHERE rl.[IDDocument]=@IDDocument
		AND (rl.ContDebit like ContAB +'%')

	INSERT INTO [AngajamentBugetarDetaliu]
           ([IDAB]
           ,[IDJurnal]
           ,[ArtAl]
           ,[Suma]
           ,[Clasif])
     SELECT @IDAB,sd.IDJurnal,REPLACE(ContCredit, ContAB+'.','') ArtAl ,
     rl.valoareLei,'' FROM 
     Rulaje rl JOIN [RulajeDoc] sd ON sd.IDDocument=rl.IDDocument
     JOIN ExBugSetup ebs ON ebs.CodUnitate = rl.CodUnitate
		WHERE rl.[IDDocument]=@IDDocument
		AND (rl.ContCredit like ContAB +'%')  
		         
	GOTO Gata
Eroare:
	 RAISERROR ('Nu a fost definit responsabilul pentru viza CFP1',16,-1)
Gata:

END



GO



IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[exbug_StergePACsiAB]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_StergePACsiAB]
GO

CREATE PROCEDURE [dbo].[exbug_StergePACsiAB]
	-- Add the parameters for the stored procedure here
	@IDPAC int, 
	@IDAB int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DELETE FROM PropunereAngajareCheltuialaDetaliu WHERE IDPAC=@IDPAC
	DELETE FROM PropunereAngajareCheltuiala WHERE IDPAC=@IDPAC
	
	DELETE FROM AngajamentBugetarDetaliu WHERE IDAB=@IDAB
	DELETE FROM AngajamentBugetar WHERE IDAB=@IDAB
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Semnaturi' AND COLUMN_NAME = 'LaComanda')
BEGIN
ALTER TABLE dbo.Semnaturi ADD
	LaComanda bit NOT NULL CONSTRAINT DF_Semnaturi_LaComanda DEFAULT 1,
	LaReceptie bit NOT NULL CONSTRAINT DF_Semnaturi_LaReceptie DEFAULT 1
END
GO


IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[InchidereConturiVenituriCheltuieli]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InchidereConturiVenituriCheltuieli]
GO

CREATE PROCEDURE [dbo].[InchidereConturiVenituriCheltuieli] 
	-- Add the parameters for the stored procedure here
	@DataDoc DATETIME,
	@DeLaData DATETIME,
	@PanaLaData DATETIME,
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @NrDocAdd int
	DECLARE @LastNrDoc bigint
	SET NOCOUNT ON;
    BEGIN TRAN
	INSERT INTO [RulajeDoc]
	([ValoareLei],[ValoareValuta],[NumarDoc],[DataDoc],[NumarNota],[TipFormula],[IDJurnal]
	,[Observatii],[xUser],[xHost],[CodUnitate])   
	SELECT
	ValoareLei,
	0 AS ValoareValuta,
	NumarDoc+RowNumber NumarDoc,
	@DataDoc AS DataDoc,
	'IC' AS NumarNota,
	2 AS TipFormula,
	IdJurnal,
	CASE WHEN Cont = '6' THEN 'Inchidere cont de cheltuieli' WHEN Cont='7' THEN 'Inchidere cont de venituri' ELSE '' END AS Observatii,
	system_user AS xUser
	,host_name() AS xHost,
	@CodUnitate AS CodUnitate
	FROM (SELECT 
		ABS(SUM(soldd)) ValoareLei,
		Cont,IDJurnal,(SELECT Max(NumarDoc) FROM [RulajeDoc] WHERE [CodUnitate]=@CodUnitate) NumarDoc,
		ROW_NUMBER() OVER (ORDER BY IDJurnal) RowNumber FROM
			(SELECT SUM(a.ValoareLei) soldd, dbo.GetContClass(ContDebit) Cont,IDJurnal FROM Rulaje a
			JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
			WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData AND a.[CodUnitate] = @CodUnitate
			GROUP BY dbo.GetContClass(ContDebit),IDJurnal
			UNION
			SELECT -SUM(a.ValoareLei) soldd, dbo.GetContClass(ContCredit) Cont,IDJurnal FROM Rulaje a
			JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
			WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData  AND a.[CodUnitate] = @CodUnitate
			GROUP BY dbo.GetContClass(ContCredit),IDJurnal) t1
		WHERE Cont>='6' AND Cont<='7' AND soldd<>0
		GROUP BY Cont,IDJurnal) t2
	WHERE ValoareLei<>0
	SET @NrDocAdd = @@ROWCOUNT	
	
    PRINT(@@IDENTITY)
	PRINT(@NrDocAdd)
	PRINT(@DeLaData)
	PRINT(@PanaLaData)
	
    --PRINT(@LastNrDoc)
	IF @@ERROR <>0  GOTO EroareDoc
		INSERT INTO [Rulaje]
		([ContDebit],[ContCredit],[CodValuta],[IDDocument] ,[ValoareValuta],[Curs],[ValoareLei],[DataDocJust]
			,[TipDocJust] ,[NumarDocJust] ,[ExplicatiiDocJust] ,[NrCrt],[CodUnitate])
			SELECT 
			CASE WHEN SCont = '7' THEN Cont WHEN SCont='6' THEN t3.ContDebit ELSE 'N/A' END ContDebit,
			CASE WHEN SCont = '7' THEN t3.ContCredit WHEN SCont='6' THEN Cont ELSE 'N/A' END ContCredit,
			0 AS CodValuta,
			NumarDoc-@NrDocAdd+Rownumber AS IDDocument,
			0 AS ValoareValuta,
			0 AS Curs,
			ValoareLei,
			@DataDoc AS DataDocJust,
			'IC' AS TipDocJust,
			0 AS NumarDocJust,
			'Inchidere Conturi'  AS ExplicatiiDocJust,
			0 AS NrCrt,
			@CodUnitate AS CodUnitate
			FROM (SELECT 
					CASE WHEN SCont='6' THEN SUM(soldd) ELSE -SUM(soldd) END ValoareLei,SCont,[CodUnitate],
					Cont,IDJurnal, @@IDENTITY AS NumarDoc,
					DENSE_RANK() OVER (ORDER BY IDJurnal,SCont) RowNumber FROM
						(SELECT SUM(a.ValoareLei) soldd, ContDebit Cont,dbo.GetContClass(ContDebit) SCont,IDJurnal,a.[CodUnitate] FROM Rulaje a
						JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
						WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData AND a.[CodUnitate]=@CodUnitate
						GROUP BY ContDebit,dbo.GetContClass(ContDebit),IDJurnal,a.[CodUnitate]
						UNION
						SELECT -SUM(a.ValoareLei) soldd, ContCredit Cont,dbo.GetContClass(ContCredit) SCont,IDJurnal,a.[CodUnitate] FROM Rulaje a
						JOIN RulajeDoc b ON a.IdDocument=b.IdDocument 
						WHERE b.DataDoc>=@DeLaData AND b.DataDoc <=@PanaLaData  AND a.[CodUnitate]=@CodUnitate
						GROUP BY ContCredit,dbo.GetContClass(ContCredit),IDJurnal,a.[CodUnitate]) t1
					WHERE dbo.GetContClass(Cont)>='6' AND dbo.GetContClass(Cont)<='7' AND soldd<>0
					GROUP BY Cont,SCont,IDJurnal,[CodUnitate]) t2 
                    JOIN ConturiCoresp t3 
                    ON ((t3.ContDebit = t2.Cont) OR (t3.ContCredit = t2.Cont)) AND t3.IDJurnal = t2.IdJurnal AND t3.CodUnitate=t2.CodUnitate
			   	  					
		WHERE ValoareLei<>0
		IF @@ERROR <>0  GOTO EroareRul
		COMMIT TRAN
	GOTO Gata
	EroareDoc:
		ROLLBACK
		RAISERROR('Eroare la generarea documentului ',16,-1)
		return
	EroareRul:
		ROLLBACK
		RAISERROR('Eroare la generarea rulajului ',16,-1)
		return

	Gata:
END

GO



/****** Object:  StoredProcedure [dbo].[fares_GenereazaD394Facturi]    Script Date: 07/16/2011 13:49:43 ******/
IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[fares_GenereazaD394Facturi]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaD394Facturi]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaD394Facturi]
	@CodUnitate smallint,
	@DataStart datetime,
	@DataEnd datetime,
	@cumparari bit	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT MAX(b.CUI) as CUI,sum(c.ValoareTotala+ c.ValoareTVA) as Total,
	Sum(c.ValoareTotala )  as TotalValoare,sum(c.ValoareTVA)  as TotalValoareTVA FROM 
	[Documente] a JOIN Parteneri b ON a.IDPartener = b.IDPartener JOIN
	[DocumenteDetaliu] c ON a.[IDDocument]=c.IDDocument
	JOIN [RegimFiscal] r ON c.IDRegimFiscal = r.IDRegimFiscal
	WHERE a.[CodUnitate]=@CodUnitate AND
	DataDoc>= @DataStart AND DataDoc<=@DataEnd  AND RTRIM(LTRIM(b.[AtributFiscal]))='RO'
	AND ((@cumparari=1 AND a.IDTipDocument=0) OR (@cumparari=0 AND a.IDTipDocument=1))
	AND r.CuTVA=1
	GROUP BY a.IDDocument
END


GO


